topic Re: Calculating total percentage when suppressing rows in QlikView

Calculating total percentage when suppressing rows

john_duffy — Wed, 02 Apr 2014 17:58:24 GMT

Hello All.

I have a requirement to suppress all rows in a chart based on the value of one of the expressions. A fairly standard request.

I can suppress the rows using dimension limits or a hidden expression, however, I cannot seem to get the total correct for an expression that shows a percentage.

I have attached a sample application to demonstrate the issue.

The application has 4 charts:

Chart 1 - All rows displayed. No condition to suppress rows.

Chart 2 - Rows suppressed using an expression. Expression totals calculated using Sum of Rows.

Chart 3 - Rows suppressed using dimension limits. Expression totals calculated using Expression Total.

Chart 4 - Rows suppressed using dimension limits. Expression totals calculated using Sum of Rows.

When suppressing rows, I cannot get the Cost / Sales expression to show the correct value. The value should be 15 / 50 = 30%.

Also, when suppressing rows and using Total Mode Expresson Total, the totals for all columns are not correct. Suppressed row values are included in the total.

The total I want in the chart should be: Total Sales - 50, Total Cost - 15, Cost / Sales - 30%.

Any help with these two issues would be greatly appreciated.

Thanks,

John.

Re: Calculating total percentage when suppressing rows

Gysbert_Wassenaar — Wed, 02 Apr 2014 18:41:36 GMT

See attached qvw.

Re: Calculating total percentage when suppressing rows

john_duffy — Wed, 02 Apr 2014 19:26:23 GMT

Thanks for the suggestion Gysbert.

I will try it in my actual application to verify performance.

I did try the following calculated dimension with the suppress when value is null option selected, however, it was too big of a hit on performance:

if(aggr(sum(Field A) - sum(Field B),dim1,dim2,dim3,dim4) > 0,1,null())

It will take a while to set up and reload. I will get back to you with the results.

Thanks again.

Re: Calculating total percentage when suppressing rows

john_duffy — Thu, 03 Apr 2014 14:31:14 GMT

Hi Gysbert.

The performance of your suggested code was great when I applied it to my application.

Thank you very much for you help.

John.