Forecast from Actual

[venkyraj]

How can we calculate forecast based on actual value?. Like current hour/date/month vs previous year same period actual?. Ex-if user selects the date(01/Jun/2022) then it should take forecast value(actual value) of previous year's (01/Jun/2021) and hour should be >now().[till now will be actual].

Dimension is drilldown- date>hour.

Measure1 - actual [sum(actual)]

Measure 2- Forecast [sum({<year={max(year)-1},date={max(date)-365},hour={>now()}>}actual)

 Can anyone help me to achieve this.

[stevedark]

Hi @venkyraj 

Just to clarify that I understand what you are trying to achieve...

It's currently 11pm on the 1st June 2022, so you want the forecast to be whatever value you did from 11pm on the 1st June 2021 - to work out an expected value until the end of day?

The expression you have for the Forecast needs some dollar expansion, in order to calculate the values for the sets. Also, taking off 365 days will not always give you the same day in the previous year, due to leap years.

The expression would therefore be more like:

sum({<year={$(=max(year)-1)},date={"$(=date(addmonths(max(date)-12), 'DD MMM YYYY'))"},hour={">$(=hour(now()))}>}actual)

Note that the date format (DD MMM YYYY) must exactly match the format of the date in your data model.

There is a problem with the logic though, I think, that if you are looking at a date other than today in the data you will get a whole days worth of data, and then your forecast would be for the remainder of a day that has completed? If you are looking at forecasting to the end of the current day, then it may be better to use now() when creating the set for all fields.

The other thing to watch out for is the fact that the 1st June will fall on a different day of the week each year, as the number of days in the year doesn't divide by seven. I don't know what line of business you are in, but it is likely that if you are picking up last years values from a Saturday to forecast the rest of day for a Monday the values could be very different.

You might find that just going for the date which is 364 days behind the latest is better, as that will always fall on the same day of the week, and remove any day-of-week bias you may have in your data.

Hope that all makes sense.

Cheers,
Steve

[venkyraj]

Thanks for your detailed response. But the above expression is not worked and attaching the qvf file, sample data and required trend chart image as well. 

here the 1pm is the current time for current date and post that is forecast from previous year same date.

[venkyraj]

@stevedark FYR

[stevedark]

Hi @venkyraj 

There was quite a lot that needed to happen in order to fix this up.

First of all, the dimension on the chart you had was on date, when in the image the dimension is time.

Before the time could be used as a dimension though the granularity of the time needed to be adjusted or you would have too many data points on the chart. I've created Hourly, Half Hourly, Ten Minute and Five Minute versions in the load script. I've also created a Day field, as this is used in the set analysis:

LOAD
"Date",
Day(Date) as Day,
Sales,
"year",
"Month",
"Time",
hour(Time) as Hour,
date(floor(Time / (1/24/12)) * (1/24/12), 'hh:mm') as Time5,
date(floor(Time / (1/24/6)) * (1/24/6), 'hh:mm') as Time10,
date(floor(Time / (1/24/2)) * (1/24/2), 'hh:mm') as Time30
FROM [lib://desktop/comparision (2).xlsx]
(ooxml, embedded labels, table is Dates);

Also in the load script, we need to know what time the data was last reloaded, as we are using that to decide when to flip from this year to last years data. This can be put into a variable:

let vReloaded = (now()-today());

We could use now() in the front end, but I have seen problems with that in the past.

The set analysis expressions then need to match on the current date and the time being less than current for the today values, and then being the same day and month in the previous year and later than the time for the forecast:

sum({<Date={"$(=max(Date))"},Hour={"<$(=Hour(vReloaded))"}>}Sales)

and 

sum({<Day={"$(=Day(max(Date)))"},Month={"$(=month(max(Date)))"},year={$(=year(max(Date))-1)},Hour={">=$(=Hour(vReloaded))"}>}Sales)

These expressions can be used in the 2022 and 2021 measures on your chart respectively. Ensure that the dimension on the chart is the same as the time value in the set analysis (in this case Hour). The code for the minute intervals is a bit more complicated - but it is in the attached file so you can work it backwards.

The charts for this look like this:

I can't see a sensible way to remove the zero values for the items later in the day and even if we could there would be a jump between the current year and the prior. I would therefore recommend just having one expression and having both years in there:

That then gives a single continuous line, but we need to apply the colour ourselves, by colouring by expression:

=if(Hour >= hour(vReloaded), rgb(230,0,0), rgb(0,0,230))

You should be able to put a vertical reference line in the chart also, to show when the line moves from actual to forecast.

The application with all of this in is attached.

My reservations above about picking a different day of the week from the previous year still stand though.

Hope that helps move you forward.

Steve

[venkyraj]

@stevedark 

Day : if we select 3/june/2022 then it should show previous year june data i.e 4/june/2021 - 30/june/2021.

Time/Hour : If we are in 4.45PM then till the 4.45PM is Actual from current selected date and After 4.45PM should be coming from previous year and same date as Forecast.

In below chart if we select 3/june/2022(Date) and Hour is current hour then it should show previous year june data i.e 3/june/2021 with after current hours data as forecast. but its not giving in the below graph.

Note : Date->Hour is the drilldown dimension.

[stevedark]

Sorry, I missed that you wanted the whole months values in there. Adjust the set analysis to only filter on Month and Year and set the set to ignore the date (Date=). As follows:

sum({<Date=,Day=,Month={"$(=month(max(Date)))"},year={$(=year(max(Date)))},Hour={"<$(=Hour(vReloaded))"}>}Sales)

and 

sum({<Date=,Day=,Month={"$(=month(max(Date)))"},year={$(=year(max(Date))-1)},Hour={">=$(=Hour(vReloaded))"}>}Sales)

Plumb those expressions into the dashboard and you should be good.

Steve