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