Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
Not applicable

Close forecast month

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.

PurposeNoCostCenterNoDepartmentNoAccountJan-13Feb-13Mar-13
xx
4000110111113010$123 123$636 345$23 423
4000110111123010$234$3 453$34 534
4000110211113010$234 234$567$2 342
4000110211123010$5 454$6 786$1 236
4000110311113010$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.

Tags (2)
2 Replies
whiteline
Honored Contributor II

Re: Close forecast month

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.

Not applicable

Re: Close forecast month

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))))