Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jmcdermott
Contributor III
Contributor III

Hiding some columns in a chart but showing others of the same dimension

Good morning Qlik Community,

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(Act/Fcst tab).  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)
2 Replies
jmcdermott
Contributor III
Contributor III
Author

All,

I got the hiding of columns to work by adding some text to the load script.  In my actuals table I added

if(trans_date < '12/1/2019',1,0) as hide_amount,

//and

'act' as act_bgt,

In the budget tables I have 12 of them, 1 for each period.  I added

'bgt' as act_bgt,
//and
'0' as hide_amount,  //for periods 1-8 and '1' as hide_amount for periods 9-12.

 

Then in Set Analysis my expression is Sum({<FiscalYear ={2019}, hide_amount={1}>}dom_amount).  

 

This was able to get me the columns that I need but I need to change '0' and '1' in 2 of  the bgt tables each month and the if(trans_date < '12/1/2019',1,0) to '1/1/2020',.  What I am struggling with now is get the 12 month Budget total to show, if I don't want the detail by period to show? 

Brett_Bleess
Former Employee
Former Employee

Have a look at the following Design Blog post, not exactly the same as what you are doing, but I am hopeful it may help:

https://community.qlik.com/t5/Qlik-Design-Blog/Creating-a-Trial-Balance-Sheet-in-QlikView/ba-p/14720...

The only other thing I think would be to use Set Analysis to get your total... There are quite a few Design Blog posts on Set Analysis items, here is the base URL for future reference, you can use that to search etc.

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Sorry I do not have something better for you.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.