Budget vs. Actual (date selection in actual)

[aneelfazwani]

Hello,

I believe a lot has been written on comparing budget vs. actual (went through generic keys by Henric). Somehow I am not getting it through. Here is the requirement:

1. Actuals are at Branch, Class and Date level

2. Budgets are at Branch, Class and Month level.

On front-end, a date is selected which show should that date's data (actual) and budget for whole month. I've created a fact table for actual and linked it with another table for budget. A time table is connected with time calendar through date field and budget table is connected with Actual through concatenation of Branch, Class and Month Year.

Through this, when a date is selected, it only shows data for budget when Actual data is present in actual table. It should always show full month budget for branch and class against any date selected for actuals.

How do I apply generic keys in this scenario? I am not sure if I should concatenate both tables or can this be done without it.

Actual linked to Budget by: Branch-Class-Month-Year

Actual linked to Calendar: ActualDate

Kindly help!

[swuehl]

You probably know this document and sample attached, but have you tried like Henric demonstrated in his sample, concatenating the fact tables and creating the link table?

Generic keys

[marcus_sommer]

To link Actuals and Budgets is quite difficult even if there is no differences between the granularities of the data. Then quite often there are Actuals which didn't have Budgets and reverse - this could be solved with a linktable which contains the distinct keys from both tables.

But much easier is it to concatenate both tables into one table by resolving the different period-levels. For this you could create a date from your month per makedate(year, month, 1) as Date and to compare the Actuals with the Budgets for the current month you used a Forecast of Actuals (is the most used type by us). And the other way is to break down the monthly Budget values on a (work) daily level.

- Marcus

[jyothish8807]

Hi Aneel,

I would recommend to group Actual at Month level as part from date. So both Actual and Budget will have same granularity.

The you can easily do a compression.

Regards

KC

[Anonymous]

Hi,

You can use in following manner:

// Creation of a Master Calendar

Let varMinDate = num(MakeDate(2015,1,1));

Let varMaxDate = num(MakeDate(2016,12,31));

TempCalendar:

LOAD

               $(varMinDate) + Iterno()-1 As Num,

               Date($(varMinDate) + IterNo() - 1) as TempDate

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

NoConcatenate

MasterCalendar:

Load date(TempDate) as Master_Date,

num(date(TempDate)) as Master_Date_Num,

Year(TempDate) as Master_Year,

Month(TempDate) as Master_Month,

MonthName(TempDate) as Month,

num(monthname(TempDate)) as Master_Monthname_Num,

num(monthname(TempDate)) as %Master_Monthname_Num

Resident TempCalendar

;

DROP Table TempCalendar;

Let vEndDateTime=Timestamp(Now(),'YYYY-MM-DD hh:mm:ss tt');

Load

Branch,

Class,

Date  as Actual_Date,

num(subfield(num(Date),'.',1)) as Master_Date_Num,

Actual

from

Actual.qvd(qvd);

Concatenate

Load

Branch,

Class,

Date  as Budget_Date,

num(subfield(num(Date),'.',1)) as Master_Date_Num,

Budget

from

Budget.qvd(qvd);

[aneelfazwani]

The reason I can't group Actual is because the user needs to select Date on interface. Also, breaking up of budget at day level is also not a possibility

[aneelfazwani]

Thanks Nitin,

I will try this solution but I've a question: Can a user select a date? This is because there are other calculations (MTD, YTD) which are to be calculated from beginning of month/beginning of month till this date.

[Anonymous]

Yes, Definately,

Crete the list boxes from Master Calendar table, for Year, Month, Quarter, Date etc.

The values will be correct

If you have any confusion of usage, please post a sample app with sample data , I will make changes ther