Calculating total percentage when suppressing rows

[john_duffy]

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.

[Gysbert_Wassenaar · Accepted solution]

See attached qvw.

[john_duffy]

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.

[john_duffy]

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.