Data forecasting in line chart

[HugoR]

Hello community,

I have a specific forecasting to implement and can't figure out how to do it.

Here is the request:

I have to calculate the difference between the sales revenue of this year and the year before. More specificaly, the sales revenue from 2020-07-01 to today-1 compare with year-1.

From that calculation I make a cumulative line chart as follow.

But now comes the tricky part. I have to calculate the average sales revenue from the last 7 days and create a forecast line until 2020-10-31 based on that.

The calculation is pretty simple: get the last cumulative value of the sales revenue and for every day until deadline, add the average value from the last 7 days and substract the sales revenue from last year. But I can't figure out how to implement that in my chart.

Here is my expression in the chart:

=
sum({<Station=,sk_date= {">=20200701<=$(=Date(Date(Today()-1),'YYYYMMDD'))"}, [Type de compte]={'SALE'},[Ledger transaction référence] = e({1<[Type de compte]={'UNPAID'}>})>} -[Montant caisse balancé])-
sum({<Station=,sk_date= {">=20190701<=$(=Date(AddYears(Date(Today()-1), -1), 'YYYYMMDD'))"}, [Type de compte]={'SALE'},[Ledger transaction référence] = e({1<[Type de compte]={'UNPAID'}>})>} -[Montant caisse balancé]) 

And the average from last 7 days:

=(
sum({<Station=,sk_date= {">=$(=Date(Date(Today()-7),'YYYYMMDD'))<=20201031"}, [Type de compte]={'SALE'},[Ledger transaction référence] = e({1<[Type de compte]={'UNPAID'}>})>} -[Montant caisse balancé])-
sum({<Station=,sk_date= {">=$(=Date(AddYears(Date(Today()-7), -1), 'YYYYMMDD'))<=$(=Date(AddYears(Today(), -1), 'YYYYMMDD'))"}, [Type de compte]={'SALE'},[Ledger transaction référence] = e({1<[Type de compte]={'UNPAID'}>})>} -[Montant caisse balancé]) )/7

The field sk_date is the date in the form of 'YYYYMMDD'. This is the way to handle dates in the document and I can't change it.

I have tried many things already:

-Using variables to store my chart expression and avg expression then

=if(sk_date >= Date(Today(),'YYYYMMDD') and sk_date <= 20201031,vDeltaRevenue+(vAVG7daysRevenue*(Date - today())))

- I try splitting my expression to add the AVG in the "this year revenue sum" like this:

=
sum({<Station=,sk_date= {">=20200701<=20201031"}, [Type de compte]={'SALE'},[Ledger transaction référence] = e({1<[Type de compte]={'UNPAID'}>})>} -[Montant caisse balancé]) 
+ ((
sum({<Station=,sk_date= {">=$(=Date(Date(Today()-7),'YYYYMMDD'))<=20201031"}, [Type de compte]={'SALE'},[Ledger transaction référence] = e({1<[Type de compte]={'UNPAID'}>})>} -[Montant caisse balancé])-
sum({<Station=,sk_date= {">=$(=Date(AddYears(Date(Today()-7), -1), 'YYYYMMDD'))<=$(=Date(AddYears(Today(), -1), 'YYYYMMDD'))"}, [Type de compte]={'SALE'},[Ledger transaction référence] = e({1<[Type de compte]={'UNPAID'}>})>} -[Montant caisse balancé]) )/7)
-
sum({<Station=,sk_date= {">=20190701<=$(=Date(AddYears(Today(), -1), 'YYYYMMDD'))"}, [Type de compte]={'SALE'},[Ledger transaction référence] = e({1<[Type de compte]={'UNPAID'}>})>} -[Montant caisse balancé]) 

-Searching through the forum I came accross this topic wich was kind of what I was looking for but I got lost trying to figure out how to use Linest_m() and Linest_b() functions.

I've been using QV for about a year but never came across such a complexe request.

Is there a simple way to make my forecast?

Thank you in advance,

Hugo

[sunny_talwar]

I think in combination of using Linest functions, you might need to use RangeSum Above or As Of Table to first get the average. It might be helpful if you are able to share a sample because without that we can just give general direction.

[johnca]

In addition to Sunny's I got it to work but I had to create the forecasting dates in the script to do so. I took my max date and added, in my case, 150 days to it. One of the expressions was (derived from that example)

Round(
LINEST_M(total aggr(if(Count({$<ForecastFlag={0}>}Distinct ICCID),Count({$<ForecastFlag={0}>}Distinct ICCID)), AlertMonth), AlertMonth) * AlertMonth
+
LINEST_B(total aggr(if(Count({$<ForecastFlag={0}>}Distinct ICCID),Count({$<ForecastFlag={0}>}Distinct ICCID)), AlertMonth), AlertMonth)
,1)

HTH,

John

[HugoR · Accepted solution]

Thanks for the guidance but I finaly managed to find something  that suit my need in a different direction and much simpler for my document management.

I handled it all in the chart with the following expression:

=
sum({<Station=,sk_date= {">=20200701<=$(=Date(Date(Today()-1),'YYYYMMDD'))"}, [Type de compte]={'SALE'},[Ledger transaction référence] = e({1<[Type de compte]={'UNPAID'}>})>} -[Montant caisse balancé])

+sum({<sk_date={">$(=Date(Date(Today()-1),'YYYYMMDD'))<=20201031"}>}vAVG7daysRevenue)
-
sum({<Station=,sk_date= {">=20190701<=$(=Date(AddYears(Date(Today()-1), -1), 'YYYYMMDD'))"}, [Type de compte]={'SALE'},[Ledger transaction référence] = e({1<[Type de compte]={'UNPAID'}>})>} -[Montant caisse balancé]) 

This is basicaly how I would  calculate it on paper and it works in my chart. Playing with the background color and dates I can show the forecast in a different color.

I hope this can help someone esle one day 🙂

Cheers,

Hugo