Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
SK28
Creator
Creator

Qlik sense - How to calculate Total & Percentage of Totals in Table

Hi guys,

Need some Suggestion on how to calculate Totals & Percentage of Totals in Qliksense Table ( Not Pivot Table) 

I have a requirement to calculate Totals & Percentage of Totals as shown below.(rows with are in bold)

here is the excel formal:

Totals = "=SUM(D32:D40)" Total for Del column is 9.43+0.41+0.41+0.90+1.04+1.35+0.46+15.41+0.25 = 29.65

Total percentage = IF($H$41>0,D41/$H$41,"")  the 29.55/160.17 = 18.5% (Total of Del/Total of Target)

Similarly for all other columns

Can you please help me on this, Thanks in Advance

 

City Del Los P Total   Target Solu 
Amst 9.43 24.89 0.81 35.14 38.12 -2.98
C 0.41 1.52   1.93 3.32 -1.39
EAS 0.41 7.22 0.04 7.67 10.00 -2.33
LAS 0.90 3.85 0.04 4.80 8.15 -3.35
MP 1.04 3.37 0.12 4.52 4.64 -0.12
U 1.35 25.32 0.75 27.42 28.39 -0.97
US 0.46 3.62   4.08 7.83 -3.75
Guj 15.41 28.29 3.32 47.03 54.92 -7.89
Holland 0.25 4.19 1.23 5.67 4.80 0.87
Total (need help here) 29.65 102.27 6.32 138.25 160.17 -21.92
Percentage of Target (Need help here) 18.5% 63.9% 3.9% 86.3% 100.0% 13.7%
2 Solutions

Accepted Solutions
vinieme12
Champion III
Champion III

As below

 

Main:
Load * inline [
City,Del,Los,P,Total ,Target,Solu
Amst,9.43,24.89,0.81,35.14,38.12,-2.98
C,0.41,1.52, ,1.93,3.32,-1.39
EAS,0.41,7.22,0.04,7.67,10.00,-2.33
LAS,0.90,3.85,0.04,4.80,8.15,-3.35
MP,1.04,3.37,0.12,4.52,4.64,-0.12
U,1.35,25.32,0.75,27.42,28.39,-0.97
US,0.46,3.62, ,4.08,7.83,-3.75
Guj,15.41,28.29,3.32,47.03,54.92,-7.89
Holland,0.25,4.19,1.23,5.67,4.80,0.87
];

DummyDim:
Load * inline [
Dim
1
2
3
];

 

In Chart

Dimension

=Pick(Dim,City,'Total','Total%')

 

Measures

Del

=Pick(Dim
,num(Sum(Del),'#,##0.00')
,num(Sum(Del),'#,##0.00')
,num(Sum(Del)/Sum(TOTAL Target),'#,##0.01%')
)

 

Los

=Pick(Dim
,num(Sum(Los),'#,##0.00')
,num(Sum(Los),'#,##0.00')
,num(Sum(Los)/Sum(TOTAL Target),'#,##0.01%')
)

 

repeat the same for the rest of the measure and set number  formatting to measure expression

qlikCommunity1.PNG

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

SK28
Creator
Creator
Author

Here is the Solution:

Step -1 Add Action Select all values in Dim & "LOCK IT"

Step 2 - Add this script

TAG FIELDS "Dim"  With $HIDDEN; 

doing this we can achieve the solution - Dim hidden, all values selected & locked , Clear all doesnt work for DIM

View solution in original post

3 Replies
vinieme12
Champion III
Champion III

As below

 

Main:
Load * inline [
City,Del,Los,P,Total ,Target,Solu
Amst,9.43,24.89,0.81,35.14,38.12,-2.98
C,0.41,1.52, ,1.93,3.32,-1.39
EAS,0.41,7.22,0.04,7.67,10.00,-2.33
LAS,0.90,3.85,0.04,4.80,8.15,-3.35
MP,1.04,3.37,0.12,4.52,4.64,-0.12
U,1.35,25.32,0.75,27.42,28.39,-0.97
US,0.46,3.62, ,4.08,7.83,-3.75
Guj,15.41,28.29,3.32,47.03,54.92,-7.89
Holland,0.25,4.19,1.23,5.67,4.80,0.87
];

DummyDim:
Load * inline [
Dim
1
2
3
];

 

In Chart

Dimension

=Pick(Dim,City,'Total','Total%')

 

Measures

Del

=Pick(Dim
,num(Sum(Del),'#,##0.00')
,num(Sum(Del),'#,##0.00')
,num(Sum(Del)/Sum(TOTAL Target),'#,##0.01%')
)

 

Los

=Pick(Dim
,num(Sum(Los),'#,##0.00')
,num(Sum(Los),'#,##0.00')
,num(Sum(Los)/Sum(TOTAL Target),'#,##0.01%')
)

 

repeat the same for the rest of the measure and set number  formatting to measure expression

qlikCommunity1.PNG

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
SK28
Creator
Creator
Author

Hi,

This works thanks, But the problem is when the user selects any value in CITY dimension, the total & Total% are disappearing

example user clicks on US

US 0.46 3.62 is showing, when User clears the city filter, total & total% are not showing up, once he clears all the filters then it's showing.

 

Is there a way to show the TOTAL & TOTAL% even with & without selections?  

SK28
Creator
Creator
Author

Here is the Solution:

Step -1 Add Action Select all values in Dim & "LOCK IT"

Step 2 - Add this script

TAG FIELDS "Dim"  With $HIDDEN; 

doing this we can achieve the solution - Dim hidden, all values selected & locked , Clear all doesnt work for DIM