Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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.
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
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,
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