Running total without %CELL

Question

Question

Evgeny Shvarov · Mar 31, 2016

#InterSystems IRIS BI (DeepSee) #Analyzer #MDX

Hi!

There is an example in Samples which shows how to implement 'running total' measure: "Pareto Chart for Category" dashboard.

To calculate running total this pivot uses calculated measure with %Cell expression:

Measures.[Revenue as Percent of Total] + %CELL(0,-1)

And the MDX looks like:

WITH MEMBER [MEASURES].[Revenue as Percent of Total] AS 'Measures.[Amount Sold] / %MDX("select Measures.[Amount sold]on 1 from holefoods","%CONTEXT","filters|columns")',FORMAT_STRING='##.##%;;;;' MEMBER [MEASURES].[Revenue Percent Cumulative] AS 'Measures.[Revenue as Percent of Total] + %CELL(0,-1)',FORMAT_STRING='##.##%;;;;' SELECT {[Measures].[Amount Sold],[MEASURES].[REVENUE PERCENT CUMULATIVE]} ON 0,NON EMPTY ORDER([Product].[P1].[Product Category].Members,Measures.[Amount Sold],BDESC) ON 1 FROM [HoleFoods]

DeepSee specialists! I wonder, is there any way to make pivots with 'running totals' without %CELL expression?

Thank you in advance!

Discussion (3)0

Log in or sign up to continue

Evgeny Shvarov · Jun 8, 2016

It seems Alex Koblov suggested solution for this in this thread

0 0

score 1 · Answer 1 · 2016-03-31T17:48:37-04:00

Sure. You could use %CELLZERO instead:

WITH  MEMBER [MEASURES].[Revenue as Percent of Total] AS 'Measures.[Amount Sold] / %MDX("select Measures.[Amount sold] on 1 
from holefoods","%CONTEXT","filters|columns")',FORMAT_STRING='##.##%;;;;' 
MEMBER [MEASURES].[Revenue Percent Cumulative] AS 'Measures.[Revenue as Percent of Total] + %CELLZERO(0,-1)',
FORMAT_STRING='##.##%;;;;' SELECT {[Measures].[Amount Sold],[MEASURES].[REVENUE PERCENT CUMULATIVE]} ON 0,
NON EMPTY ORDER([Product].[P1].[Product Category].Members,Measures.[Amount Sold],BDESC) ON 1 FROM [HoleFoods]

Jokes aside, why do you need to remove %CELL usage?

score 0 · Answer 2 · 2016-03-31T20:58:56-04:00

With %CELL you should be always aware when you add any dimension, measure or property in MDX if you need to change %CELL expression too due to new columns rows or because of deletions of rows and cols.