catador Posted November 18, 2006 Share Posted November 18, 2006 Hi to all!! I'm very confused with this!!! look... i've this data: qty unitcost costlast difunitcost %varUC period coslastperiod lastperLine1 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!! Link to comment Share on other sites More sharing options...
swood Posted November 21, 2006 Share Posted November 21, 2006 Could you show us your schema and the MDX you are trying to use. ShermanJasperSoft Link to comment Share on other sites More sharing options...
catador Posted November 21, 2006 Author Share Posted November 21, 2006 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> Link to comment Share on other sites More sharing options...
swood Posted November 21, 2006 Share Posted November 21, 2006 And the MDX? ShermanJasperSoft Link to comment Share on other sites More sharing options...
catador Posted November 21, 2006 Author Share Posted November 21, 2006 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 ROWSfrom [Existencias]where [FechaDocumento].[All FechaDocumentos].[2006].[2] Thanks for any help, i'm really confused Link to comment Share on other sites More sharing options...
swood Posted November 24, 2006 Share Posted November 24, 2006 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)"/> ShermanJasperSoft Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now