Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
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

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
Support (Former)
Support (Former)

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.