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

[vamshi_1241]

I used the aboved attached qvw as binary in the Qliksense app and created a pivot table with same attributes and expression,  which is showing correctly with the above two expressions. I am facing the issue with the QV pivot table. (Business wants this report in QV only). Is there any difference between QV Pivot and QS Pivot? 

@sunny_talwar  @Michael_Tarallo @hic@swuehl 

[sunny_talwar]

You can try this

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

[vamshi_1241]

Thanks Sunny for your response but issue still exists in pivot table.

[sunny_talwar]

Can you point out the issue in the image below?

[vamshi_1241]

Hi Sunny, 

data is showing wrong with the expression. The source file has data for Item - A200, Org - 100, and plan- 1 only on 6/19/2019 which week1 (6/17/2019). With the cumulative logic, it should display 2 for other dates too but with this expression, it is showing 10 for 1st week and 14 for next week. Here is the expected output - 

But when drilling down to day level - all other weeks are showing 0.

[sunny_talwar]

Is this from the qvw that I have shared? I am seeing this

I see 6/24/2019 to show 14 even when week of 06/17/2019 is expanded.... 

[vamshi_1241]

If you see week 6/24,  7/1, and so on, there is no data for those dates so it should be 2 instead of 14 for those weeks too

[sunny_talwar]

To see 2 for those, you need to expand those weeks my friend

Sum of 7 2s across all days of 6/24/2020 adds up to 14. I guess you would want to see sum, right? or am I missing something?

[vamshi_1241]

I am looking for cumulative data. Since we don't have after 6/19, 2 should be displayed for other dates.

Interestingly, I don't see any issues in Qliksense if we use this qvw as binary and using below expression pivot table.

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

Not sure why this is not working in Qlikview