topic Re: Budget Achievement. in QlikView

Budget Achievement.

Sun, 28 Jun 2015 06:09:30 GMT

Dear Experts,

I want to calculate budget achievement in my analysis.

tables are shown below,(SAMPLE DATA)

BUDGET,

CLA_CODE JAN FEB MAR ...... ....... DEC TOTAL

AA 100,000 150,000 200,000 125,000 2,000,000

BB 10,000 25,000 50,000 30,000 375,000

CC 50,000 50,000 50,000 50,000 600,000

In this case there are many sub products which mainly not allocated the budgets.

Ex,

CLA_CODE PRD_CODE

AA AA1

AA AA2

AA AA3

BB BB1

BB BB2

BB BB3

CC CC1

CC CC2

In the data base records are available as shown below,

DATE POL_NO PRD_CODE SALE_AMOUNT

01/01/15 AA10015001 AA1 10,000.00

02/01/15 BB10015001 BB1 1,000.00

03/01/15 CC10015001 CC1 1,500.00

04/01/15 BB20015001 BB2 2,500.00

04/01/15 AA10015002 AA1 5,000.00

05/01/15 CC30015001 CC3 3,000.00

05/01/15 CC20015001 CC2 7,500.00

05/01/15 CC10015002 CC1 2,000.00

06/01/15 BB10015002 BB1 1,000.00

06/01/15 BB10015003 BB1 1,200.00

So I'm looking forward a suitable formula to calculate the Budget Achievement depending on above tables.

Kind attention and reply would be highly appreciated.

Rgds,

Priyantha.

Re: Budget Achievement.

marcus_sommer — Sun, 28 Jun 2015 07:55:18 GMT

There are two general different approaches. For both the same is to load the budget-data with a cross-table statement The Crosstable Load.

First approach:

Create from each month a date which you set to the monthstart: makedate(year, month, 1) as date. Then you could these table concatenate with the sales fact-table in which you create a forecast-column of your sales-amounts. Then you could within the gui compare your forecast-column with the budget-column on month- and CLA_CODE-level.

Second approach:

This is more advanced then you need to divide your monthly budget-values to a date- and PRD_CODE-level. For this you need to know (extra tables or variables) how many dates and PRD_CODE are available and looped through them with for-loops or you used subfield() or while-loops within the loads, see:

Generating Missing Data In QlikView

How to populate a sparsely populated field

And in general for the topic to fifferent granularities: Fact Table with Mixed Granularity.

- Marcus

Re: Budget Achievement.

Tue, 30 Jun 2015 07:22:30 GMT

Dear Marcus,

In my model don't want to look in to the budgeted figures daily. But I want to look at this monthly basis. Major problem I facing here in there is no budgeted figures in each and every policy.

The other thing is budget is not allocated to each and every PRD_CODE.

Please can you explain your idea simply.

Rgds.

Priyantha.

Re: Budget Achievement.

marcus_sommer — Tue, 30 Jun 2015 08:32:48 GMT

Yes it's a quite common case that not each area from sales had a budget and conversely and further that sales and budget have a different granularity. The easiest way to solve this is my above mentioned first approach which looked simplified:

YourBudget:

Crosstable(Month, BUDGET_AMOUNT, 1) Load * BudgetSource; // year-field is missing

SalesAndBudget:

Load Date, CLA_CODE, PRD_CODE, SALE_AMOUNT From YourSales;

concatenate (SalesAndBudget)

Load CLA_CODE, makedate(Year, Month, 1) as Date, BUDGET_AMOUNT Resident YourBudget;

drop tables YourBudget;

Then you could simply use CLA_CODE and Date (or another field by linking this field with a master-calendar) and use sum(SALE_AMOUNT) and sum(BUDGET_AMOUNT) as expressions (and maybe a rate between them). You need not worry about any linking sales and budgets - they are within the same table and it worked great.

Without using a forecast for the sales data you could of course only compare previous periods and not the current period included - and a forecast could be quite simple like:

sales / working days MTD * working days Month total

- Marcus

Re: Budget Achievement.

Tue, 30 Jun 2015 09:52:59 GMT

Dear Marcus,

As a beginner in the QV, Let me now,

Which part i must modified in my script?

and which part I must modified in my pivot table?

Rgds,

Priyantha,

Re: Budget Achievement.

marcus_sommer — Tue, 30 Jun 2015 11:20:06 GMT

What didn't work? If your data looks like you described them it should work. Maybe you take a new application only with the above loadings (avoid potentially problems) and goes then further step by step using the tableviewer and within the gui a few listboxes, a tablebox (to look on the data) and a chart with simple sum() to check the results.

Have also a look here: Get started with developing qlik datamodels.

- Marcus