Berita basi banget (basbang) sebenarnya, tapi masih sering muncul di beberapa milis dan masih menimbulkan banyak keraguan dan ketakutan. Untuk memberi ‘sedikit’ ketenangan, posting email dari salah satu milis yang saya ikuti mungkin bisa membantu. Konfirmasi ke pihak BSA Indonesia sebaiknya dilakukan, just to make sure. Pesan forward yang saya baca di milis netzaga beberapa… Continue reading
IIf function often used in MultiDimensionalExpression (MDX), It’s quiet simple to implement single block of IIf block. This an example of Multiple IIf logic block. Example1:
|
1 2 3 4 |
IIf(Measures.[Store Sales] > 50000, "Good", IIf(Measures.[Store Sales] > 30000, "Average", IIf(Measures.[Store Sales] > 10000, "Poor", "No"))) |
A logical script in MDX to perform an “If..Then.. Else..” block.
|
1 |
IIf(«Logical Expression», «Numeric/String Expression1», «Numeric/String Expression2») |
Example1:The following example returns 0 if Measures.CurrentMember is an empty cell, 1 otherwise:
|
1 |
IIf(IsEmpty(Measures.CurrentMember), 0, 1) |
Example2:The following string returns the string “Yes” if Measures.CurrentMember is an empty cell, the string, “No” otherwise:
|
1 |
IIf(IsEmpty(Measures.CurrentMember), "Yes", "No") |
Example3:
|
1 2 3 4 5 |
IIf( [Scenario].CurrentMember IS [Scenario].[Budget], [Measures].[Budget Unit Sold], [Measures].[ActualUnit Sold] ) |
Example4:Average Sales (Measure) By Time Day (Dimension)
|
1 2 3 4 5 6 7 |
IIf ( ([Time].currentmember.level.name = "(All)" OR [Time].currentmember.level.name = "Year" OR [Time].currentmember.level.name = "Quarter" OR [Time].currentmember.level.name = "Month" OR [Time].currentmember.level.name = "Day" ),AVG(Descendants(Time.CurrentMember,[Time].[Day]),Measures.[Sales]),0) |
Source:… Continue reading
There’s a few scenario to calculate a percentage of a measure in OLAP CUBE (Analysis Service 2000). A Percentage is common calculation in MDX , and it can be solved by divide a measure to total value (Grand Total). Sample 1:Measures.[Value Contribution to Total]:
|
1 |
(Measures.[Value] / (Measures.[Value], Time.[All Time], Products.[All Products],…) |
Sample 2:Measures.[Value Contribution to Total]:
|
1 |
(Measures.[Value] / (Measures.[Value], Products.[All Products],Warehouses.[All Warehouses]) |
Sample 3:Measures.[Product Contribution to… Continue reading