Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Act | Act | Act | Act | Act | Act | Act | Bgt | Bgt | Bgt | Bgt | Bgt | Total Act&Fcst | Total Budget | % Act& Fcst /Budget | O/(U) Budget | ||
Account | Description | Period 1 | Period 2 | Period 3 | Period 4 | Period 5 | Period 6 | Period 7 | Period 8 | Period 9 | Period 10 | Period 11 | Period 12 | Variance | |||
Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar | Total | Total | ||||
920000 | Headcount | 19.0 | 19.0 | 20.0 | 22.0 | 21.5 | 22.5 | 25.0 | 25.0 | 25.0 | 25.0 | 25.0 | 25.0 | 22.8 | 25.0 | 91% | (2.2) |
930000 | Contract Labor Headcount | 0.9 | 0.1 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.1 | 0.0 | 0% | 0.1 |
611040 | Salaries | 50,557 | 60,797 | 50,130 | 63,050 | 56,334 | 54,324 | 69,525 | 63,682 | 66,784 | 70,107 | 61,025 | 67,464 | 733,781 | 791,682 | 93% | ($57,901) |
611020 | Overtime | 3,255 | 5,815 | 565 | 662 | 307 | 820 | 2,500 | 2,500 | 2,500 | 2,500 | 2,500 | 2,500 | 26,424 | 30,000 | 88% | ($3,576) |
SUBTOTAL - Salaries | 53,813 | 66,612 | 50,695 | 63,712 | 56,642 | 55,144 | 72,025 | 66,182 | 69,284 | 72,607 | 63,525 | 69,964 | 760,205 | 821,682 | 93% | ($61,477) | |
613010 | Year End Bonus | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 17,500 | 0 | 0 | 0 | 17,500 | 17,500 | 100% | $0 |
SUBTOTAL - Bonus | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 17,500 | 0 | 0 | 0 | 17,500 | 17,500 | 100% | $0 | |
616010 | 401(K) Matching Contribution | 2,255 | 2,348 | 2,010 | 2,055 | 3,158 | 2,086 | 2,920 | 2,675 | 2,805 | 2,945 | 2,563 | 2,833 | 30,653 | 33,251 | 92% | ($2,598) |
SUBTOTAL - Expenses on Allowance for Retirement | 2,255 | 2,348 | 2,010 | 2,055 | 3,158 | 2,086 | 2,920 | 2,675 | 2,805 | 2,945 | 2,563 | 2,833 | 30,653 | 33,251 | 92% | ($2,598) | |
618010 | Payroll Taxes | 4,524 | 4,769 | 3,766 | 4,948 | 4,305 | 4,177 | 5,319 | 4,872 | 5,109 | 6,835 | 5,926 | 6,309 | 60,859 | 65,525 | 93% | ($4,666) |
SUBTOTAL - Legal Welfare | 4,524 | 4,769 | 3,766 | 4,948 | 4,305 | 4,177 | 5,319 | 4,872 | 5,109 | 6,835 | 5,926 | 6,309 | 60,859 | 65,525 | 93% | ($4,666) | |
----------- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- | ----------- | ||
TOTAL - Salary & Related | 60,591 | 73,729 | 56,471 | 70,715 | 64,105 | 61,407 | 80,264 | 73,728 | 94,698 | 82,387 | 72,014 | 79,107 | 869,217 | 937,957 | 93% | ($68,740) | |
Avg Personel Exp Per Hdct | 3,039 | 3,862 | 2,824 | 3,214 | 2,982 | 2,729 | 3,211 | 2,949 | 3,788 | 3,295 | 2,881 | 3,164 | 37,925 | 37,518 | 101% | $407 | |
620030 | Group Medical Premiums | 15,620 | 8,867 | 13,780 | 23,310 | 19,457 | 19,151 | 20,704 | 20,704 | 20,704 | 20,704 | 20,704 | 20,703 | 224,406 | 248,442 | 90% | ($24,036) |
620031 | Group Dental Premiums | 302 | 467 | 162 | 400 | 95 | 219 | 456 | 456 | 455 | 455 | 456 | 455 | 4,378 | 5,466 | 80% | ($1,088) |
620082 | Life & Disab Insurance | 394 | 132 | 1,166 | 282 | -844 | 410 | 122 | 122 | 122 | 122 | 122 | 122 | 2,271 | 1,461 | 155% | $810 |
620084 | Worker's Compensation | 856 | 856 | 856 | 856 | 856 | 856 | 537 | 536 | 537 | 537 | 537 | 537 | 8,353 | 6,438 | 130% | $1,915 |
620090 | Uniforms/Work Shoes/Glasses | 0 | 0 | 0 | 0 | 0 | 0 | 400 | 400 | 400 | 400 | 400 | 400 | 2,400 | 4,800 | 50% | ($2,400) |
620091 | Medical Transport | 0 | 0 | 0 | 70 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 70 | 0 | 0% | $70 |
SUBTOTAL - Welfare Expense | 17,172 | 10,321 | 15,964 | 24,918 | 19,564 | 20,636 | 22,217 | 22,217 | 22,217 | 22,217 | 22,217 | 22,217 | 241,878 | 266,607 | 91% | ($24,729) |
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?
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:
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