topic Re: Pivot Table Total - Correct until collapsed in QlikView

Pivot Table Total - Correct until collapsed

chris1987 — Thu, 05 Apr 2018 09:15:28 GMT

Hi,

I have a pivot table that has a function to calculate each line - which works Perfect when expanded but when the group is collapsed the value changes. I know why this is, it's because all of the values are rolled up on the subtotal row and then the logic is applied on to this new row.

e.g.

Expanded:

Customer	Item	2017 Sales	2018 Sales	Like for Like (Formula)	Like for Like (Value)
Cust1	12345	£1000	£2000	=if(not isnull([2017 Sales]) and not isnull([2018 Sales]),[2018 Sales],null())	£2000
	98765	-	£1000	=if(not isnull([2017 Sales]) and not isnull([2018 Sales]),[2018 Sales],null())
	54321	£500	£500	=if(not isnull([2017 Sales]) and not isnull([2018 Sales]),[2018 Sales],null())	£500
	99999	£200	-	=if(not isnull([2017 Sales]) and not isnull([2018 Sales]),[2018 Sales],null())

So you can see that when the group is expanded that in the "Like for Like Value" column the total is £2500, but when you collapse it you get the following:

Collapsed:

Customer	Item	2017 Sales	2018 Sales	Like for Like (Formula)	Like for Like (Value)
Cust1		£1700	£3500	=if(not isnull([2017 Sales]) and not isnull([2018 Sales]),[2018 Sales],null())	£3500

I need to find a way to keep the £2500 when the row is collapsed. I was thinking aggr() but I'm a bit unsure how to do this.

Any help would be appreciated

Cheers

Chris

Re: Pivot Table Total - Correct until collapsed

thevingo — Thu, 05 Apr 2018 09:23:30 GMT

I guess, because your IF conditions work at Summary-level & not at detail/row-level.

That's why 2018 sales for item 98765 is also added to the Total

Re: Pivot Table Total - Correct until collapsed

thevingo — Thu, 05 Apr 2018 09:23:50 GMT

try SET expression

Re: Pivot Table Total - Correct until collapsed

olivierrobin — Thu, 05 Apr 2018 09:38:14 GMT

hello

maybe you couls try

sum(=if(not isnull([2017 Sales]) and not isnull([2018 Sales]),[2018 Sales],0)

Re: Pivot Table Total - Correct until collapsed

chris1987 — Thu, 05 Apr 2018 09:53:11 GMT

Thanks, I agree this is why it's happening but don't think SET expression will fix it? I need this to be dynamic as the user can select different period using filters etc.