help with variable please!!

[catador]

Hi to all!!

 

I'm very confused with this!!! look... i've this data:

 

qty unitcost costlast difunitcost %varUC

period coslastperiod lastper

Line1 7678 527.4 2273.3 -1745.9 -331

Prod1 7561 506.11 0 506.11 100

Prod2 0 0 0 0 0

Prod3 0 0 0 0 0

Prod4 3 523.33 0 523.33 100

Prod5 63 2273.3 2273.3 0 0

Prod6 51 1527.76 0 1527.76 100

 

dif unit cost last period it's unitcost - costlastcost

 

and %varUClasper it's a formula as (difunitcost * 100)/unitcost

 

if you see in Line1 the value of %varUClastper its -331 what if whe think its ok. But this % isn't representative of %varUC Products under Line1... i need a variable what help in navigation and search where %var... it's more big and detect cost variations...

 

i really anyone understand my problem and can help me to create a real usefull variable.

 

Thanks!!

[swood]

Could you show us your schema and the MDX you are trying to use.

 

 

Sherman

JasperSoft

[catador]

Ok... this is

 

<?xml version="1.0"?>

<Schema name="Puig">

<Dimension name="Producto">

<Hierarchy hasAll="true" primaryKey="PRODUCTO_KEY">

<Table name="Dim_Producto_Existencias"/>

<Level column="NombreDivision" name="Nombre Division"/>

<Level column="NombreCasa" name="Nombre Casa"/>

<Level column="NombreMarca" name="Nombre Marca"/>

<Level column="CodigoReferencia" name="Codigo Referencia">

<Property name="NombreReferencia" column="NombreReferencia"/>

</Level>

</Hierarchy>

</Dimension>

<Dimension name="TipoMovimiento">

<Hierarchy hasAll="true" primaryKey="TIPO_MOVIMIENTO_KEY">

<Table name="Dim_Tipo_Movimiento"/>

<Level column="TipoMovimiento" name="TipoMovimiento"/>

<Level column="SubTipoMovimiento" name="SubTipoMovimiento"/>

</Hierarchy>

</Dimension>

<Dimension name="FechaDocumento" type="TimeDimension">

<Hierarchy hasAll="true" primaryKey="CIERRE_KEY">

<Table name="dim_cierre_ex"/>

<Level name="Ano" column="Ano" type="Numeric" uniqueMembers="true" levelType="TimeYears"/>

<Level name="Mes" column="CodigoMes" uniqueMembers="false" type="Numeric" levelType="TimeMonths"/>

</Hierarchy>

</Dimension>

<Cube name="Existencias">

<Table name="Tabla_Fact_DM_EXISTENCIAS"/>

<DimensionUsage foreignKey="TIPO_MOVIMIENTO_KEY" name="TipoMovimiento" source="TipoMovimiento"/>

<DimensionUsage foreignKey="PRODUCTO_KEY" name="Producto" source="Producto"/>

<DimensionUsage foreignKey="CIERRE_KEY" name="FechaDocumento" source="FechaDocumento" />

<Measure aggregator="sum" column="cantidad" formatString="Standard" name="Qty"/>

<Measure aggregator="sum" column="valor" formatString="#,###.00" name="ValorTrx"/>

<Measure aggregator="count" column="cantidad" name="QMovs"/>

<Measure aggregator="sum" column="costopromedio" formatString="#,###.00" name="CuTrx"/>

<CalculatedMember name="CuActual" dimension="Measures"

formatString="Currency"

formula="([Producto].CurrentMember,[Measures].[ValorTrx])/([Producto].CurrentMember,[Measures].[Qty])"/>

 

<CalculatedMember name="CuLP" dimension="Measures" formatString="Currency"

formula="([Measures].[CuActual],[FechaDocumento].PrevMember)" >

<CalculatedMemberProperty name="SOLVE_ORDER" value="1"/>

</CalculatedMember>

 

<CalculatedMember name="VarCuLP" dimension="Measures"

formatString="#,###.00"

formula="([FechaDocumento].CurrentMember,[Measures].[CuActual])-([FechaDocumento].CurrentMember.PrevMember,[Measures].[CuActual])">

<CalculatedMemberProperty name="SOLVE_ORDER" value="1"/>

</CalculatedMember>

 

<CalculatedMember name="VarCierreLY" dimension="Measures"

formula="([FechaDocumento].CurrentMember,[Measures].[CuActual]) - (ClosingPeriod(Mes,[FechaDocumento].CurrentMember.Parent.PrevMember), [Measures].[CuActual])">

<CalculatedMemberProperty name="SOLVE_ORDER" value="3"/>

</CalculatedMember>

 

</Cube>

</Schema>

[swood]

And the MDX?

 

 

 

Sherman

JasperSoft

[catador]

this is:

 

with member [Measures].[Var%CuLP] as 'Iif([Producto].CurrentMember.Level.Name = "Codigo Referencia ",(([Measures].[VarCuLP] * 100.0) / ([Producto].CurrentMember, [Measures].[CuLP])),(([Measures].[VarCuLP] * 100.0) / ([Producto].CurrentMember, [Measures].[CuLP])))',

FORMAT_STRING = IIf(([Measures].[Var%CuLP] < 15.0), IIf(([Measures].[Var%CuLP] < 10.0), IIf(([Measures].[Var%CuLP] < 0.0), "|#|style=green","|#|style=yellow"),"|#|style=yellow"), "|#|style=red")

select {[Measures].[Qty], [Measures].[CuActual], [Measures].[CuLP], [Measures].[VarCuLP], [Measures].[Var%CuLP], [Measures].[VarCierreLY] } ON COLUMNS,

{[Producto].[All Productos], [Producto].[All Productos].[FPC ], [Producto].[All Productos].[PBB ], [Producto].[All Productos].[Producto T], [Producto].[All Productos].[servicio ]} ON ROWS

from [Existencias]

where [FechaDocumento].[All FechaDocumentos].[2006].[2]

 

Thanks for any help, i'm really confused

[swood]

This seems weird to me.

 

Code:

<CalculatedMember name="CuActual" dimension="Measures"
formatString="Currency"
formula="([Producto].CurrentMember,[Measures].[ValorTrx])/([Producto].CurrentMember,[Measures].[Qty] )"/>

 

 

Try

 

<CalculatedMember name="CuActual" dimension="Measures"

formatString="Currency"

formula="iif([Measures].[Qty] > 0, [Measures].[ValorTrx]/[Measures].[Qty], 0)"/>

 

 

Sherman

JasperSoft