Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
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