topic Re: How to calculate forecast data in straight table in QlikView

How to calculate forecast data in straight table

abc_18 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I want to calculate forecast value based on user selection.I am using master calendar where I have month , Year as filter

so if user selects year=15 and month =Jan, then from Feb-15 to May-15 it should come as forecast data.how to calculate that field in expression.

Gysbert_Wassenaar — Thu, 10 Mar 2016 14:18:51 GMT

Maybe sum({<Date={'>$(=Max(Date))'}>}Value)

If not, please post a small qlikview document that demonstrates the problem.

sunny_talwar — Thu, 10 Mar 2016 14:21:26 GMT

Is the forecast field available in the database or is this a calculation?

Digvijay_Singh — Thu, 10 Mar 2016 15:38:31 GMT

See if it works for you -

same expression as suggested by gwassenaar ,removed zeros from Present Column.

abc_18 — Thu, 10 Mar 2016 15:46:05 GMT

Forecast filed I need to calculate based on value and date

abc_18 — Thu, 10 Mar 2016 16:26:46 GMT

HI Digvijay,

How to apply same forecast logic in below condition:

if(DIVISION='PUMA',sum({<FYEAR=>})Value,sum({<Year=>})Value)

Date filed linking is with master calendar (FYEAR,Year values are from Master calendar)

Digvijay_Singh — Thu, 10 Mar 2016 17:04:25 GMT

Try like this - you should verify the result by not changing label of expression, it will show if set expression is failing.

if(DIVISION='PUMA',sum({1<FYEAR=,Date={'>$(=Max(Date))'}>}Value),sum({<Year=>}Value))

abc_18 — Fri, 11 Mar 2016 06:54:02 GMT

Hi Gysbert,

I tried your expression to get previous year value.

=if(DIVISION='PUMA',sum({<FYear={$(=(FYear)-1)}>}Value),sum({<Year={$(=(Year)-1)}>}Value))

but I am getting '0' value in previous year column.

Gysbert_Wassenaar — Fri, 11 Mar 2016 13:19:35 GMT

As I posted earlier: If not, please post a small qlikview document that demonstrates the problem.