Qlik NPrinting Discussions

[ConnorSteph]

Hi all,

I have a table in Pixel Perfect that contains Sales, Budget, Variance to Budget in $, and Variance to Budget in % as the four columns. The problem arises in the totals.

I can sum the first three columns with no problems using the Summary tool, but Summing (or averaging) a column of percentages does not work properly - I need to be able to calculate the Total Variance to Budget in % as a product of the Total Variance to Budget in $ divided by the Total Budget. Is there a way to do this?

If this is impossible I can create a new table within Sense the contains only the totals, and insert that below, however it would be much better if I could do the above as I have to do this for multiple tables.

Many thanks,

Connor

[Lech_Miszkiewicz]

Since you are able to calculate total for Sales and Budget why dont you use those to calculate % on total?

[ConnorSteph]

Hi Lech,

That's actually what I'm asking how to do/if it is possible. Apologies if the question was a simple one, I am new to Pixel Perfect.

How would I do this?

[Ruggero_Piccoli · Accepted solution]

Hi,

You cannot calculate the total of a percentage as sum of the single percentages of the column. You have to create a new calculated field in the PixelPerfect template:

1. right click on the table row in the levels node and select Add a calculated field
2. in the expression insert the formula sum(field1)/sum(field2) (adapt the formula based on your needs) 
3. drag and drop the new calculated field in the template

In the following example I calculate the percentage of each row compared with the total sum of all rows:

Best Regards,

Ruggero

[ConnorSteph]

Hi Ruggero,

Thanks, that worked perfectly! Appreciate it.