Values not summing up while 'No Split' on Year in Pivot Table

[P_Kumar]

Hi,

I am using QS Sep'2018 version.  I have created a Pivot Table with Year and Month as dimensions and has a measure.  When I don't split the pivot table, the measure values are being displayed perfect per each month but the sum(measure) is not being displayed for Year and showing 'Zero' per Year when I don't split on the Year.

Can some one pls help with the issue.  Thanks.

[zzyjordan]

Hi, Praveen
can you provide some more details like screenshot to help understand your issue?

ZZ

[P_Kumar]

Thank you here is the issue in detail:

When we expand in on the year, we get to see all the values for each month but when we collapse, it shows zero for Year. Please advice.  Also, the issue only persist 2017.  

[zzyjordan]

Still can't reproduce in my side. it may because of your data structure or your expression used. Can you share a qvf?

ZZ

[P_Kumar]

Hi,

I have attached a sample qvf.  When you expand on 'Year', you see sales and Range sum values for each 'Month' but when you collapse, you see 'Rangesum' as 'zero'.

Also, though there are values associated for each month and Year but when you select some specific 'Year' and 'Month' from filters, you see the 'Rangesum' as 'Zero' (eg: Year=2016, Month=4)

Please advise.

--- When I am attaching the qvf, it's prompting following message and not able to attach the qvf.  Any other way I can send the file? Pls advise. --

'The contents of the attachment doesn't match its file type'

[zzyjordan]

try add your qvf to a zip file

[P_Kumar]

Thanks.. attached a zip file.

[zzyjordan · Accepted solution]

try this expression RangeSum(Before(sum(Sales),1,1)) This will fix the collapse issue for 2017, but for 2016, as you use before in the expression, there is nothing before 2016, it returns 0 which I think is correct unless you don't need to use before function. ZZ

[P_Kumar]

Thank you.. this is working!!