Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
jmcdermott
New Contributor III

Subtotals in Pivot table chart and hiding column from chart but maintaining the sort based on field

Good afternoon All,

I am  trying to get a Chart in Qlikview that look like this output below.   I have attached the qvw with the data.  I have a sorting field call "Sorting" that gets the data in the order that I want but I don't want the field to be displayed but want the sort to happen based on that field.  Also I am wanting subtotals at "SL_Account_Group", when I do this under presentation tab and check show partial sums, it creates the subtotal after every SL_Bgt_Acct_No.  You can see it in the qvw, can someone help me solve my issue?

Thanks,

Josh 

 

611040Salaries59,95069,160
611020Overtime2,5002,500
    
 SUBTOTAL - Salaries62,45071,660
    
613010Year End Bonus00
    
 SUBTOTAL - Bonus00
    
616010401(K) Matching Contribution2,5182,905
    
 SUBTOTAL - Expenses on Allowance for Retirement2,5182,905
    
618010Payroll Taxes4,6345,299
    
 SUBTOTAL - Legal Welfare4,6345,299
   -----------  ----------- 
 TOTAL - Salary & Related69,60279,864
 Avg Personel Exp Per Hdct2,7843,195
    
620030Group Medical Premiums20,70420,704
620031Group Dental Premiums455456
620082Life & Disab Insurance122122
620084Worker's Compensation537536
620090Uniforms/Work Shoes/Glasses400400
    
 SUBTOTAL - Welfare Expense22,21722,217
 
 

 

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: Subtotals in Pivot table chart and hiding column from chart but maintaining the sort based on fi

Hello Josh,

 

You need to remove the Sorting dimension from your chart.

You need to add the following sort order to the top Dimension in your dimensions list like this:

image.png

 

Then you need to find the field you want to add subtotals to (SL_Bgt_Acct_No I think it is in your case) and on Presentation -> check the Show Partial Sums for that field only like this:

image.png

 

You will get this result (qvw is attached):

image.png

I hope that helps!

Kind regards,

S.T.

View solution in original post

3 Replies
Highlighted
Partner
Partner

Re: Subtotals in Pivot table chart and hiding column from chart but maintaining the sort based on fi

Hello Josh,

 

You need to remove the Sorting dimension from your chart.

You need to add the following sort order to the top Dimension in your dimensions list like this:

image.png

 

Then you need to find the field you want to add subtotals to (SL_Bgt_Acct_No I think it is in your case) and on Presentation -> check the Show Partial Sums for that field only like this:

image.png

 

You will get this result (qvw is attached):

image.png

I hope that helps!

Kind regards,

S.T.

View solution in original post

Highlighted
jmcdermott
New Contributor III

Re: Subtotals in Pivot table chart and hiding column from chart but maintaining the sort based on fi

S.T.

Thanks it works as expected.  

Highlighted
jmcdermott
New Contributor III

Re: Subtotals in Pivot table chart and hiding column from chart but maintaining the sort based on fi

S.T or anyone else , I have a second table now that I want to combine current fiscal year actuals( 1st 7 fiscal periods) and remaining periods as bgt (periods 8-12).  Also the totals and %'s and difference columns out to the right.  What I want the output to look like is in the paste below.  Attached is the qvw with the details.  If anyone could help it would be much appreciated.

Thanks,

Josh

 

  ActActActActActActActBgtBgtBgtBgtBgtTotal Act&FcstTotal Budget% Act& Fcst /BudgetO/(U) Budget
AccountDescription                   Period 1Period 2Period 3Period 4Period 5Period 6Period 7Period 8Period 9Period 10Period 11Period 12   Variance
                                     AprMayJunJulAugSepOctNovDecJanFebMar    Total    Total  
                  
920000Headcount                     19.019.020.022.021.522.525.025.025.025.025.025.022.825.091%              (2.2)
930000Contract Labor Headcount0.90.10.00.00.00.00.00.00.00.00.00.00.10.00%                0.1
                  
611040Salaries50,55760,79750,13063,05056,33454,32469,52563,68266,78470,10761,02567,464733,781791,68293%($57,901)
611020Overtime3,2555,8155656623078202,5002,5002,5002,5002,5002,50026,42430,00088%($3,576)
                  
 SUBTOTAL - Salaries53,81366,61250,69563,71256,64255,14472,02566,18269,28472,60763,52569,964760,205821,68293%($61,477)
                  
613010Year End Bonus0000000017,50000017,50017,500100%$0
                  
 SUBTOTAL - Bonus0000000017,50000017,50017,500100%$0
                  
616010401(K) Matching Contribution2,2552,3482,0102,0553,1582,0862,9202,6752,8052,9452,5632,83330,65333,25192%($2,598)
                  
 SUBTOTAL - Expenses on Allowance for Retirement2,2552,3482,0102,0553,1582,0862,9202,6752,8052,9452,5632,83330,65333,25192%($2,598)
                  
618010Payroll Taxes4,5244,7693,7664,9484,3054,1775,3194,8725,1096,8355,9266,30960,85965,52593%($4,666)
                  
 SUBTOTAL - Legal Welfare4,5244,7693,7664,9484,3054,1775,3194,8725,1096,8355,9266,30960,85965,52593%($4,666)
   -----------  -----------  -----------  -----------  -----------  -----------  -----------  -----------  -----------  -----------  -----------  -----------  -----------  -----------  -----------  ----------- 
 TOTAL - Salary & Related60,59173,72956,47170,71564,10561,40780,26473,72894,69882,38772,01479,107869,217937,95793%($68,740)
 Avg Personel Exp Per Hdct3,0393,8622,8243,2142,9822,7293,2112,9493,7883,2952,8813,16437,92537,518101%$407
                  
620030Group Medical Premiums15,6208,86713,78023,31019,45719,15120,70420,70420,70420,70420,70420,703224,406248,44290%($24,036)
620031Group Dental Premiums302467162400952194564564554554564554,3785,46680%($1,088)
620082Life & Disab Insurance3941321,166282-8444101221221221221221222,2711,461155%$810
620084Worker's Compensation8568568568568568565375365375375375378,3536,438130%$1,915
620090Uniforms/Work Shoes/Glasses0000004004004004004004002,4004,80050%($2,400)
620091Medical Transport00070000000007000%$70
                  
 SUBTOTAL - Welfare Expense17,17210,32115,96424,91819,56420,63622,21722,21722,21722,21722,21722,217241,878266,60791%($24,729)