Rangesum issue in QV pivot table

[vamshi_1241]

Hi Guys,

I am facing one issue while cumulating data in Qlikview Pivot table -

Issue - I added week and date are columns in the pivot table. Cumulation is working well and showing the correct numbers at the day level. If we don't drill down weeks into dates, it is showing the correct numbers at the week levels as well. If you expand any week, all other weeks are showing zeros instead of cumulative numbers.

Business wants to see cumulative data at week level by default and if they want they can drill down to day level. I have created a week (MON-SUN) based on the date field and added both week and date in a pivot table with a plan, org, and item as dimensions and added below expression to see cumulative data.

Rangesum(before(TOTAL sum(QTY), 0, columnno(TOTAL)))

 Rangesum(before(TOTAL sum(QTY), 0, noofcolumns(TOTAL))).

I tried with the above expressions but not working well for this situation. Attaching sample data and tested qvw for reference.

Could you please help me?

Thanks in Advance,

Vamshi

[sunny_talwar · Accepted solution]

Now that makes sense.... try this

=If(SecondaryDimensionality() = 2,

Sum(Aggr(
	RangeSum(Above(
		Sum(QTY)
	, 0, RowNo()))
, [Plan ID], [Org id], [Item Id], Date)),

Sum(Aggr(
	RangeSum(Above(
		Sum(QTY)
	, 0, RowNo()))
, [Plan ID], [Org id], [Item Id], Week))
)

[vamshi_1241]

Thanks Sunny. This is working fine