Budgets and Actuals - indicating difference in script

[QFanatic]

hi all

Please find attached very simple spreadsheet and model

so what I have here is data regarding Messaging and Rates. The messaging data is Actuals Only, where-as the Rates contains budgeted values (future dated) as well.

What I want to achieve in the script, is to set some sort of indicator, lets call it "Actual" in the record for each Type, because as you can see in the simple chart, if I just subtract 1 month, for both of these metrics, both expressions are incorrectly dated, due to the fact that budgets are future dated.

In my script, when I read both Types, I need to:

1. " Test"  the Period to see if its future dated - if it is, make indicator Actuals (e.g.) = 0.

2. If the Period is past, make indicator Actuals = 1.

3. In the Expression of the chart create something like this (For messaging, for 1 Month Back)...

=sum ({<Type = {'Message'},Period = {'$(vRepPeriod_1)'}>}Volume)

where

vRepPeriod_1 = only({<PeriodID={"$(=Max({<Actuals={'1'}>}PeriodID)-1)"},FY=>}Period)..

4. The  result needs to be correct for both Messaging and Rates.

I hope I am explaining this ok. The whole reason for this is that Actual data and Budgeted data should appear on one chart...at the same time. If I didnt need to take the future data into consideration, I could just do max(Period)-1 in both expressions for 1 month ago....(please note - I cannot use Period as a dimension in my charts - that would have been an easy win.....- I have set descriptions that I "force" in there with ValueLists)

Please may you give me some guidance? I am not sure if I should build (and how) the logic about the "Actuals" indicator in the calendar, or in each table's read....

Thank you

[Lisa_P · Accepted solution]

Reload the combined table (Messaging and Rates will be autoconcatenated into one table)

and create your flag using this script:

NewData:
Load Period,
   Volume,
   Type,
   IF(Period > Year(Today()) & Num(Month(Today()), '00'), 0,1) as Actuals
Resident Data;

Drop Table Data;

[QFanatic]

Awesome, thanks Lisa!