topic Re: Pixel Perfect - Calculated Total in Qlik NPrinting

Pixel Perfect - Calculated Total

ConnorSteph — Fri, 15 Nov 2024 22:49:18 GMT

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

Re: Pixel Perfect - Calculated Total

Lech_Miszkiewicz — Wed, 09 Jun 2021 10:41:43 GMT

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

Re: Pixel Perfect - Calculated Total

ConnorSteph — Wed, 09 Jun 2021 10:44:19 GMT

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?

Re: Pixel Perfect - Calculated Total

Ruggero_Piccoli — Wed, 09 Jun 2021 14:50:22 GMT

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:

right click on the table row in the levels node and select Add a calculated field
in the expression insert the formula sum(field1)/sum(field2) (adapt the formula based on your needs)
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

Re: Pixel Perfect - Calculated Total

ConnorSteph — Wed, 09 Jun 2021 17:36:45 GMT

Hi Ruggero,

Thanks, that worked perfectly! Appreciate it.

Re: Pixel Perfect - Calculated Total

mstoler — Fri, 19 Aug 2022 19:10:37 GMT

Hello,

I am doing something similar where I need to compute the % change.

The formula is col1/col2-1.

How can I get this to work in the totals?

Thanks,

Michael

Re: Pixel Perfect - Calculated Total

Ruggero_Piccoli — Mon, 22 Aug 2022 10:13:59 GMT

Hi @mstoler ,

The question is not clear to me, sorry. Do you want to add a new calculated column? If you want to add totals refer to my previous solution ad adapt the formula to your case.

Best Regards,

Ruggero

Re: Pixel Perfect - Calculated Total

mstoler — Mon, 22 Aug 2022 10:23:09 GMT

Hello,

Thanks I figured it out.

I need to use the sum function and then the column name. In my example my columns are already sums so I have the sum twice.

Thanks

Michael

Re: Pixel Perfect - Calculated Total

Ruggero_Piccoli — Mon, 22 Aug 2022 10:46:48 GMT

Hi,

You can create a new calculated field by using another one:

If this doesn't solve please start a new conversation with all the details of your case, like screenshots of the template, to help us to understand the specific scenario.

Best Regards,

Ruggero

Re: Pixel Perfect - Calculated Total

mstoler — Mon, 22 Aug 2022 11:06:53 GMT

Hello,

Yes the two items that I needed to take care of are:

1. I need to use the "Sum" in the functions and not just type in "Sum". Also because my columns are sums I see a nested Sum with the outer "Sum" is the function in Pixel Perfect.

2. This is in my Report Totals but because I am using the function "Sum" I don't need to to set the Summarize.

It seems to work well now.

Thank You,