topic Re: Rangesum issue in QV pivot table in QlikView

Rangesum issue in QV pivot table

vamshi_1241 — Sat, 16 Nov 2024 00:32:26 GMT

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

Re: Rangesum issue in QV pivot table

vamshi_1241 — Mon, 08 Jun 2020 08:00:00 GMT

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

Re: Rangesum issue in QV pivot table

sunny_talwar — Mon, 08 Jun 2020 13:20:27 GMT

You can try this

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

Re: Rangesum issue in QV pivot table

vamshi_1241 — Mon, 08 Jun 2020 16:13:49 GMT

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

Re: Rangesum issue in QV pivot table

sunny_talwar — Mon, 08 Jun 2020 16:30:52 GMT

Can you point out the issue in the image below?

Re: Rangesum issue in QV pivot table

vamshi_1241 — Tue, 09 Jun 2020 06:31:28 GMT

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.

Re: Rangesum issue in QV pivot table

sunny_talwar — Tue, 09 Jun 2020 11:09:10 GMT

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....

Re: Rangesum issue in QV pivot table

vamshi_1241 — Tue, 09 Jun 2020 14:41:59 GMT

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

Re: Rangesum issue in QV pivot table

sunny_talwar — Tue, 09 Jun 2020 15:33:16 GMT

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?

Re: Rangesum issue in QV pivot table

vamshi_1241 — Wed, 10 Jun 2020 06:13:16 GMT

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

Re: Rangesum issue in QV pivot table

sunny_talwar — Wed, 10 Jun 2020 11:40:12 GMT

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)) )

Re: Rangesum issue in QV pivot table

vamshi_1241 — Fri, 12 Jun 2020 08:13:46 GMT

Thanks Sunny. This is working fine