Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
PFA
Did you try using set analysis?
=Sum({<abc -= {a}, def -= {xyy}>}val)
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.
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.
Do you true nulls or do they have blank spaces like in your attachment
null or blank spaces or 0 value. there can be all 3 of these
Try this
=If(Count(TOTAL <abc, def> Aggr(If(Sum(val) > 0, ghi), abc, def, ghi)) = Count(TOTAL DISTINCT ghi), Sum(val))
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.
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))