Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am new to Qlik and would appreciate your help
I have budget numbers that don't change often which I want to compare to monthly actuals.
The data may look something like below.
Month | Region | Budget |
---|---|---|
01/01/2016 | North | $100,000 |
01/01/2016 | South | $120,000 |
03/01/2016 | North | $140,000 |
03/01/2016 | West | $50,000 |
The budget for North was 100k in Jan and Feb; it was increased in March to 140k and has stayed 140k per month till date.
Budget for South is the same per month since Jan. West was introduced in March.
I will not receive new numbers unless there is a change in the budgeted values.
Actuals are broken down by Month and Region.
I need to do a variance for each month by region using this lookup data. How can I lookup each Region and apply the budgeted value?
Thanks!
Shaheer
May be create the missing budget values for all region:
Table:
LOAD Month,
Region,
Budget
FROM
[https://community.qlik.com/thread/220410]
(html, codepage is 1252, embedded labels, table is @1);
FinalTable:
NoConcatenate
LOAD Date(AddMonths(Month, IterNo()-1)) as Month,
Region,
Budget
While AddMonths(Month, IterNo()-1) < End;
LOAD *,
Date(If(Region = Previous(Region), Previous(Month) - 1, Today())) as End
Resident Table
Order By Region, Month desc;
DROP Table Table;
May be create the missing budget values for all region:
Table:
LOAD Month,
Region,
Budget
FROM
[https://community.qlik.com/thread/220410]
(html, codepage is 1252, embedded labels, table is @1);
FinalTable:
NoConcatenate
LOAD Date(AddMonths(Month, IterNo()-1)) as Month,
Region,
Budget
While AddMonths(Month, IterNo()-1) < End;
LOAD *,
Date(If(Region = Previous(Region), Previous(Month) - 1, Today())) as End
Resident Table
Order By Region, Month desc;
DROP Table Table;
Works like a charm. Thank you!