topic Re: Rows total in Pivot table in QlikView

Rows total in Pivot table

timanshu — Wed, 16 Aug 2017 09:04:54 GMT

I have an issue. I want to show rows total in pivot table instead of expression total. Aggr does not work. Since I have some different scenario.

Please find the attached qvw and screenshot of pivot.

My logic is, I dont want to show records which have null or 0 expression value corresponding to 'kkk' and 'rrr' values of 'ghi' dimension.

According to above logic in application attached, also shown in attached screenshot in left chart, I want to hide row with value 'a' and 'xyy' in 'abc' and 'def' column respectively.

and total will be sum of values shown. so in our scenario, for mmm value in total will be 32.

I am able to hide the row as shown in right chart. But total I want to show 32 instead of 46.

Please help to resolve this.

Re: Rows total in Pivot table

ankur_abhishek — Wed, 16 Aug 2017 09:18:28 GMT

PFA

Re: Rows total in Pivot table

sunny_talwar — Wed, 16 Aug 2017 11:58:19 GMT

Did you try using set analysis?

=Sum({<abc -= {a}, def -= {xyy}>}val)

Re: Rows total in Pivot table

timanshu — Thu, 17 Aug 2017 04:51:33 GMT

Hi,

Thanks for reply I dont want to hard code and want logic in front end to be developed.

there can be many more rows and they change everytime. So logic should be dynamic.

Re: Rows total in Pivot table

timanshu — Thu, 17 Aug 2017 04:51:49 GMT

Hi,

Thanks for reply I dont want to hard code and want logic in front end to be developed.

there can be many more rows and they change everytime. So logic should be dynamic.

Re: Rows total in Pivot table

sunny_talwar — Thu, 17 Aug 2017 11:20:38 GMT

Do you true nulls or do they have blank spaces like in your attachment

Re: Rows total in Pivot table

timanshu — Thu, 17 Aug 2017 11:26:40 GMT

null or blank spaces or 0 value. there can be all 3 of these

Re: Rows total in Pivot table

sunny_talwar — Thu, 17 Aug 2017 11:46:50 GMT

Try this

=If(Count(TOTAL <abc, def> Aggr(If(Sum(val) > 0, ghi), abc, def, ghi)) = Count(TOTAL DISTINCT ghi), Sum(val))

Re: Rows total in Pivot table

timanshu — Mon, 21 Aug 2017 09:00:46 GMT

Actually I want total row also. I already did the filtering of row as you can see in right chart in my application attached. but total is wrong in that. I want total as sum of above row values. Please help.

Re: Rows total in Pivot table

sunny_talwar — Mon, 21 Aug 2017 11:09:16 GMT

Try this

=Sum(Aggr(If(Count(TOTAL <abc, def> Aggr(If(Sum(val) > 0, ghi), abc, def, ghi)) = Count(TOTAL DISTINCT ghi), Sum(val)), abc, def, ghi))