Close forecast month

[#1]

Hi,

I have forecast figures from Excel and results coming from the finance system. I want to be able to set a parameter in excel that closes the month. When the parameter is added (month closed) the the QV app should sum up the results from the finance system. Otherwise it should show the forecast from Excel. I've tried putting an x in an empty column and using this expression:

if(Budget_$(vCurrency)='x',
sum( { <_FlagPnLi={1}>}Amount_$(vCurrency)),
sum( { <_FlagPnLi={1},ForecastType={Forecast_Actuals}>} Budget_$(vCurrency)))

There's a variable for the Currency in the app, that's why I'm using $vCurrency.

This is an example table, I'm using cross table to read this in to the application and concatenating it with the results.

PurposeNo	CostCenterNo	DepartmentNo	Account	Jan-13	Feb-13	Mar-13
				x	x
4000	1101	1111	3010	$123 123	$636 345	$23 423
4000	1101	1112	3010	$234	$3 453	$34 534
4000	1102	1111	3010	$234 234	$567	$2 342
4000	1102	1112	3010	$5 454	$6 786	$1 236
4000	1103	1111	3010	$23 423	$867 967	$76 374

Not really getting it to work and also wondering if anyone has any other approach or suggestions? As it is now an x turns up in the budget figures and it's not very elegant.

[whiteline]

Hi.

If it possible I would prefer to embed the business logic into the data model.

The closed period is static and I guess you don't have the requirement to compare the fact to the past forecast.

You can assume the forecast as facts (Amount_$(vCurrency)) that just have different type (field flag).

Usually it helps to keep the expressions simply and clear to the end users.

[#3]

Thanks for the reply although I didn't really understand the answer .

Anyway, I solved it by joining a Y or N flag to the concatenated fact table that contains both the budget and actuals figures like this:

FlagTable:
left join
  LOAD * INLINE [
     PeriodBudget, _Flag_Actuals
     201309, Y
     201310, Y
     201311, Y
     201312, Y
     201401, Y
     201402, Y
     201403, N

And then changing the expression to an if() statement, somehow an set analysis expression seemed impossible:

sum(if(_Flag_Actuals = 'Y',if(_FlagPnLi=1, Amount_$(vCurrency)) ,

if(_FlagPnLi=1 and ForecastType='Forecast_Actuals', Budget_$(vCurrency))))