Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
timanshu
Creator III
Creator III

Rows total in Pivot table

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.

9 Replies
ankur_abhishek
Contributor III
Contributor III

PFA

sunny_talwar

Did you try using set analysis?

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

Capture.PNG

timanshu
Creator III
Creator III
Author

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.

timanshu
Creator III
Creator III
Author

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.

sunny_talwar

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

Capture.PNG

timanshu
Creator III
Creator III
Author

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

sunny_talwar

Try this

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

timanshu
Creator III
Creator III
Author

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.

sunny_talwar

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


Capture.PNG