Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a Pivot table where I just want to show the previous month and current month data but for the yearly data I want to include everything in calculation. As shown in the image below, the highlighted row needs to be hidden but included in the total calculations.
For the subtotal level calculation, try
Only(Aggr( Sum(AVAILABLE)/Sum (DEMAND), Period))
edit:
Attached a sample QVW
Maybe you can use Dimensionality() to check on which dimensional level the expression gets evaluated, then branch into different expressions:
=If(Dimensionality() = 1,
YourExpressionForPeriodTotal,
YourExpressionForDetailsLevel
)
Thanks for the reply swuehl.
I tried the below
if(Dimensionality()=2 ,
if(((Month='Current Month')
or (Month='Prev Month')),
(Sum(AVAILABLE)/Sum (DEMAND)),0),Sum(AVAILABLE)/Sum (DEMAND))
but it causes the Month dimension to suppress.
How have you created your month dimension and linked to your calendar months?
Could you maybe post a small sample QVW?
my calculated dimension for month is
=if((Month([REQ DEL DATE])=month(today()-30) and Year([REQ DEL DATE])=year(today()-30)),
'Current Month',
if((Month([REQ DEL DATE])=month(today()-60) and Year([REQ DEL DATE])=year(today()-60)),
'Prev Month',' '))
So 'Current Month' is not a field value, that's why the condition like
Month = 'Current Month' is never true.
I was more thinking of something like this (maybe using set analysis instead of the sum(if()) 😞
if(Dimensionality()=2 ,
Sum(if([REQ DEL DATE]>=Today()-60, AVAILABLE))/Sum (if([REQ DEL DATE]>=Today()-60,DEMAND)),
Sum(AVAILABLE)/Sum (DEMAND)
)
I tried this earlier , but this still doesn't hide the blank row in 2016-YTD period, as highlighted in my initial post.
Remove the second ELSE branch from your calculated dimension and enable 'Suppress When Value is NULL' for your calculated dimension on dimension tab:
=if((Month([REQ DEL DATE])=month(today()-30) and Year([REQ DEL DATE])=year(today()-30)),
'Current Month',
if((Month([REQ DEL DATE])=month(today()-60) and Year([REQ DEL DATE])=year(today()-60)),
'Prev Month'
))
I tried that as well, if I use suppress when value is null then it excludes the other values except the current and previous month in the calculation of the total service %.
For the subtotal level calculation, try
Only(Aggr( Sum(AVAILABLE)/Sum (DEMAND), Period))
edit:
Attached a sample QVW