Budget Achievement.

[#1]

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.

[marcus_sommer]

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

[#3]

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.

[marcus_sommer · Accepted solution]

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

[#5]

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,

[marcus_sommer]

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