Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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:
% Spend
if you haven't already.Sum([Days Logged]) / Sum([Demand (Days)])
.Use the newly created measure in your table:
% 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.
Total % Spend
.Sum([Days Logged]) / Sum([Demand (Days)])
% Spend
measure.Total % Spend
.This should correct the total percentage calculation in your table.
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
Hi
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:
Add a new measure:
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]
))
[Your Dimensions]
with the dimensions used in your table.Format the measure as a 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.