topic Re: Incorrect Percentage Total in straight table in App Development

Incorrect Percentage Total in straight table

JohnnyUtah — Tue, 09 Jul 2024 19:37:52 GMT

Hi,

I have a straight table consisting of 6 dimensions and six measures.

% Spend is simply Days Logged / Demand (Days), so I'm expecting the % Spend below to be 41.7%

% Spend is a master object formatted as %.

In the % spend column Total Function is set to Auto.

I'm fairly new to Qlik so expect there is an easy answer to this.

Thanks in advance

Re: Incorrect Percentage Total in straight table

Scotchy — Tue, 09 Jul 2024 23:42:39 GMT

The issue you're facing with the % Spend calculation in the total line of your Qlik Sense table is likely due to how the Auto total function works for percentages. Instead of calculating the total percentage as the sum of individual percentages, you want it to calculate the percentage based on the total values.

Here's how you can resolve this:

Create a new measure for the total % Spend:
- Go to the "Master Items" and create a new measure for % Spend if you haven't already.
- Use the expression Sum([Days Logged]) / Sum([Demand (Days)]).
Use the newly created measure in your table:
- Replace the current % Spend measure in your table with the new measure you created.

This way, the total % Spend will be calculated based on the total values of Days Logged and Demand (Days), ensuring the total line shows the correct percentage.

Steps to Create a New Measure:

Go to the "Master Items" panel on the left.
Click on "Measures" and then on the "+" icon to create a new measure.
Name the measure, for example, Total % Spend.
Enter the expression:

Sum([Days Logged]) / Sum([Demand (Days)])
Set the number formatting to "Number" and format as a percentage.

Replace the Measure in the Table:

Edit your table.
Remove the current % Spend measure.
Add the newly created measure Total % Spend.

This should correct the total percentage calculation in your table.

Re: Incorrect Percentage Total in straight table

JohnnyUtah — Wed, 10 Jul 2024 08:20:27 GMT

Hi,

thanks for the reply. I've tried your suggestion but it does not fix the issue. I think the issue is that my % Spend master measure is derived from two other master measure items:

My % Spend master item is derived from two other master items:

Days Logged = Sum(clockify_hours_logged/vWorkingHours)

Demand (Days) = If(GetSelectedCount(period)=0, max(sd_demand)
,If(GetSelectedCount(period)>0, (max(sd_demand)/12)*GetSelectedCount(period)))

I then create my % Spend master item

% Spend = Days Logged / Demand (Days)

Qlik will not allow me to create a master items which sums these:

sum(Days Logged) / Sum (Demand (Days)

I don't think you can create master item which sums up other master measures.

Regards

Paul

Re: Incorrect Percentage Total in straight table

Scotchy — Wed, 10 Jul 2024 23:14:20 GMT

Maybe try this approach...

You're correct that Qlik Sense does not allow you to create a master measure that aggregates other master measures directly. However, you can create the necessary aggregation in your table's measure directly.

To achieve the correct total calculation for % Spend, you can use a measure that performs the necessary calculations directly within the table. Here’s how you can proceed:

Edit your table:
- Go to the properties panel of your straight table.
Add a new measure:
- Click on "Add measure."
- In the expression editor, use the following expression:
  
  Sum(Aggr(Sum(clockify_hours_logged/vWorkingHours), [Your Dimensions])) / Sum(Aggr( If(GetSelectedCount(period)=0, max(sd_demand), If(GetSelectedCount(period)>0, (max(sd_demand)/12)*GetSelectedCount(period))), [Your Dimensions] ))
- Replace [Your Dimensions] with the dimensions used in your table.
Format the measure as a percentage:
- Set the number formatting to "Number" and choose "Percentage."

This approach uses the Aggr function to calculate the Days Logged and Demand (Days) for each dimension combination and then aggregates these values to get the correct total percentage.