2 Replies Latest reply: May 14, 2014 6:43 PM by Andreas Gumaelius RSS

    Close forecast month

    Andreas Gumaelius

      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.

        • Re: Close forecast month
          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.

          • Re: Close forecast month
            Andreas Gumaelius

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