Lookup budget data and create variance to actuals

[shaheermecci]

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

[sunny_talwar · Accepted solution]

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;

[shaheermecci]

Works like a charm. Thank you!