Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Rolling Forecast Formula

Hello together,

I Need your help again

It is the MONTH JUNE.

MonthMonthMonth
159
2610
3711
4812

table:

NatureAmountMonth
ACTUAL3Juni
FORECAST4Juli
ACTUAL5Jan
FORECAST7August

For the first formula I Need the sum of the ACTUAL figures from the JAN to JUN and

the second formula is the figures of FORECAST from the JULI to DEZ.

1. Formula

sum(Amount) how get the Sum from JAN to current month JUN into the formula

2 Formula

sum(Amount), how get the SUM from JUL to DEC into the formula.

Thanks

7 Replies
MK_QSL
Not applicable

Re: Rolling Forecast Formula

Provide sample data please...

Not applicable

Re: Rolling Forecast Formula

What sample Data you Need?

jpe
Not applicable

Re: Rolling Forecast Formula

It seems this would work.

Actuals:   sum (  {$<Nature={'ACTUAL'}>}  Amount)

Forecast:   sum (  {$<Nature={'FORECAST'}>}  Amount)

Not applicable

Re: Rolling Forecast Formula

but These formulas Shows me the whole amounts, but how can Combine the time Feature inside.

Actuals Show me the Amount (Actual) from Jan-Jun

Forecasts Show me the Amount (Forecast) from Jul-Dec.


jpe
Not applicable

Re: Rolling Forecast Formula

Ok . I think you are asking for Jan-Jun numbers for actuals and Jul-Dec numbers for forecast. Because the small data set you provided only has actuals for the jan-jun number and only forecast numbers for the jul-dec numbers the following would work. If your actual data set is different then you should post a representative data set.

If you just want to know the syntax to be more explicit with months you can try this. Add as many months in the list as you need.  

Actuals:   sum (  {$<Nature={'ACTUAL'}, Month={'Jan','Juni'}>}  Amount)

Forecast:   sum (  {$<Nature={'FORECAST'},Month={'Juli','August'}>}  Amount)

Not applicable

Re: Rolling Forecast Formula

Thank you for the formulas

But how I get the current Date into the Formular

Actuals: sum({$<Nature={'ACTUAL'}, Month={'Jan', 'Juni'} Amount)

How I get here, that 'Jan' is always the first month and  

'Juni' Shows the selected month. And I Need then the 'Jan', 'Feb', 'Mar', 'April', 'Mai', 'Juni'.

Forecast: sum({$<Nature={'FORECAST'}, Month={'Juli', 'August'}>} Amount)

Juli Shows the currentMonth+1 and August always the December.

I hope you understand what I mean.

Thanks

jpe
Not applicable

Re: Rolling Forecast Formula

In this example,  Month is a numeric field in the data model from 1 - 12  depending which month.  Current month is Septermber  which is Month=9.

If you have a Month number field in your data model you can use this:

Forecast:  sum(  {$<Month = {'> $(=num(month(today())))' }>}  Sales)

Actuals:   sum(  {$<Month = {'<= $(=num(month(today())))' }>}  Sales)