How to forecast when x axis is month(booking date). Forecast seems not to work.

[labasmuse]

Is your feature request related to a problem? Please describe.
Your instruction for the app "Sample_App_Forecasting_Simple" is very clear. The installation and the trial of the app immediately worked. Great!
However I do not know what to do when I have for the y axis an aggreation function where the Month_Date is not included: for example: " (sum({$<FSA = {EE10}>}Saldo_Buchung)+sum({$<FSA = {EA10}>}Saldo_Buchung))/sum({$<FSA = {EE10}>}Saldo_Buchung)". Here the percentual gross margin is calculated. The x axis is Month(Booking date).

Describe the solution you'd like
More examples. I think the PyTools.Prophet formula is not well explained. What do the parameters mean exactly? Easy examples would help a lot.

How to do a forecast for Month ()-functions.... I would be happy to get help....

Thank you in advance.

Kind regards
Andy

[nabeel-oz]

Hi @labasmuse , have you seen the documentation here: https://github.com/nabeel-oz/qlik-py-tools/blob/master/docs/Prophet.md

You can do a monthly forecast by passing the freq=M parameter to the PyTools.Prophet function. You do need to choose a granularity for the forecast, so having a drill down dimension would complicate things.

[labasmuse]

Hi Nabeel Yes I have seen the documentation and I tried it with drilldown and had no success. If it works somehow with a drill down dimension, it would be great. Is there a chance? My key performance indicators. Unfortunately I cannot see the Forecast curve in the diagram. "Buchungsdatum" is the daily booking date. Is there a chance to find the debug entries? Actual: (sum({$<FSA = {EE10}>}Saldo_Buchung)+sum({$<FSA = {EA10}>}Saldo_Buchung))/sum({$<FSA = {EE10}>}Saldo_Buchung) Forecast: PyTools.Prophet([Buchungsdatum], (sum({$<FSA = {EE10}>}Saldo_Buchung)+sum({$<FSA = {EA10}>}Saldo_Buchung))/sum({$<FSA = {EE10}>}Saldo_Buchung), 'freq=M, return=yhat, changepoint_prior_scale=1, debug=True') Thank you very much for a short feedback. Andy Von: Nabeel <[email protected]> Gesendet: Donnerstag, 3. September 2020 09:49 An: nabeel-oz/qlik-py-tools <[email protected]> Cc: Muss Andreas <[email protected]>; Mention <[email protected]> Betreff: Re: [nabeel-oz/qlik-py-tools] How to forecast when x axis is month(booking date). Forecast seems not to work. (#108) Hi @labasmuse<https://github.com/labasmuse> , have you seen the documentation here: https://github.com/nabeel-oz/qlik-py-tools/blob/master/docs/Prophet.md You can do a monthly forecast by passing the freq=M parameter to the PyTools.Prophet function. You do need to choose a granularity for the forecast, so having a drill down dimension would complicate things. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub<#108 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AOSB3J45JLN44ZJCVLYXUP3SD5C7JANCNFSM4QS2U5TQ>. BDO AG, mit Hauptsitz in Zürich, ist die unabhängige, rechtlich selbstständige Schweizer Mitgliedsfirma des internationalen BDO Netzwerkes. BDO SA, dont le siège principal est à Zurich, est le membre suisse, juridiquement indépendant, du réseau international BDO. BDO SA, con sede principale a Zurigo, è una società giuridicamente indipendente e autonoma, membro della rete internazionale di aziende BDO. BDO Ltd, with its statutory seat in Zurich, is the legally independent Swiss member firm of the international BDO network.

[nabeel-oz]

Hi Andy,

For the monthly granularity you're currently using the Month function which returns an integer. The Prophet function requires a datetime dimension so you'll need to use MonthName, MonthStart or MonthEnd instead. Also, your line chart seems to have the Year as a dimension. The chart to plot the forecast should take the form of one datetime dimension and multiple measures (actual, forecast, etc.).

I'd recommend that you first build separate charts for monthly and daily forecasts to decide which granularity works best for your app. The measure for the monthly forecast may look like this:

PyTools.Prophet(MonthName([Buchungsdatum]), (sum({$<FSA = {EE10}>}Saldo_Buchung)+sum({$<FSA = {EA10}>}Saldo_Buchung))/sum({$<FSA = {EE10}>}Saldo_Buchung), 'freq=M, return=yhat, debug=True')

And the daily one like this:

PyTools.Prophet([Buchungsdatum], (sum({$<FSA = {EE10}>}Saldo_Buchung)+sum({$<FSA = {EA10}>}Saldo_Buchung))/sum({$<FSA = {EE10}>}Saldo_Buchung), 'freq=D, return=yhat, debug=True')

A drilldown dimension going from month to date won't work well for forecasting as selecting a month would generate predictions based on just one month of data, which won't be of much use.

If you want to calculate a daily forecast and roll it up to the monthly level, you could use a time-aware line chart with a continuous axis and let Qlik do the aggregation. You could also pre-calculate the forecast through the Qlik load script.

By the way, your value for changepoint_prior_scale is very high. I'd keep it close to the default value of 0.05 initially.

Nabeel

[labasmuse]

Hi Nabeel, I do like your project. It is really great! Thank you very much for your detailed answer, it helped me a lot and I could do jumps…. :-) I took over the measure for the monthly forecast: PyTools.Prophet(MonthName([Buchungsdatum]), (sum({$<FSA = {EE10}>}Saldo_Buchung)+sum({$<FSA = {EA10}>}Saldo_Buchung))/sum({$<FSA = {EE10}>}Saldo_Buchung), 'freq=M, return=yhat, debug=True') Instead of "return=yhat" I used "return= y_then_yhat" because I am only interested in future dates. The results: My x axis is "=MonthStart(Buchungsdatum)" Great that I receive a forecast (orange line). Unfortunately not from future dates, but from 2017, next time from 2018 but better than nothing: [cid:[email protected]] - I tried to insert future periods (months), however I was not successful…. I cannot see future months. I tried it with "left join(table) load * Inline[….]" .to extend the x axis for the forecast period. - Your trick with the if condition for the actual data did not work for me…. (if([Month Start] < Date#('01/07/2019', 'DD/MM/YYYY'), Sum(Attendances)) I do like this idea!!! :-) I tried it too -> if([Buchungsdatum] < Date#('01/11/2019', 'DD/MM/YYYY'), (sum({$<FSA = {EE10}>}Saldo_Buchung)+sum({$<FSA = {EA10}>}Saldo_Buchung))/sum({$<FSA = {EE10}>}Saldo_Buchung)) But then I receive only the actual data from 2017. :-( [cid:[email protected]] I do appreciate your feedback!!! Kind regards Andy Von: Nabeel <[email protected]> Gesendet: Montag, 7. September 2020 04:08 An: nabeel-oz/qlik-py-tools <[email protected]> Cc: Muss Andreas <[email protected]>; Mention <[email protected]> Betreff: Re: [nabeel-oz/qlik-py-tools] How to forecast when x axis is month(booking date). Forecast seems not to work. (#108) Hi Andy, For the monthly granularity you're currently using the Month function which returns an integer. The Prophet function requires a datetime dimension so you'll need to use MonthName, MonthStart or MonthEnd instead. Also, your line chart seems to have the Year as a dimension. The chart to plot the forecast should take the form of one datetime dimension and multiple measures (actual, forecast, etc.). I'd recommend that you first build separate charts for monthly and daily forecasts to decide which granularity works best for your app. The measure for the monthly forecast may look like this: PyTools.Prophet(MonthName([Buchungsdatum]), (sum({$<FSA = {EE10}>}Saldo_Buchung)+sum({$<FSA = {EA10}>}Saldo_Buchung))/sum({$<FSA = {EE10}>}Saldo_Buchung), 'freq=M, return=yhat, debug=True') And the daily one like this: PyTools.Prophet([Buchungsdatum], (sum({$<FSA = {EE10}>}Saldo_Buchung)+sum({$<FSA = {EA10}>}Saldo_Buchung))/sum({$<FSA = {EE10}>}Saldo_Buchung), 'freq=D, return=yhat, debug=True') A drilldown dimension going from month to date won't work well for forecasting as selecting a month would generate predictions based on just one month of data, which won't be of much use. If you want to calculate a daily forecast and roll it up to the monthly level, you could use a time-aware<https://help.qlik.com/en-US/sense/June2020/Subsystems/Hub/Content/Sense_Hub/Visualizations/time-aware-charts.htm> line chart with a continuous axis and let Qlik do the aggregation. You could also pre-calculate the forecast through the Qlik load script<https://github.com/nabeel-oz/qlik-py-tools/blob/master/docs/Prophet.md#precalculating-forecasts-in-the-load-script>. By the way, your value for changepoint_prior_scale is very high. I'd keep it close to the default value of 0.05 initially. Nabeel — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub<#108 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AOSB3J5QV2NRIJGVMLDT64DSEQ57FANCNFSM4QS2U5TQ>. BDO AG, mit Hauptsitz in Zürich, ist die unabhängige, rechtlich selbstständige Schweizer Mitgliedsfirma des internationalen BDO Netzwerkes. BDO SA, dont le siège principal est à Zurich, est le membre suisse, juridiquement indépendant, du réseau international BDO. BDO SA, con sede principale a Zurigo, è una società giuridicamente indipendente e autonoma, membro della rete internazionale di aziende BDO. BDO Ltd, with its statutory seat in Zurich, is the legally independent Swiss member firm of the international BDO network.

[nabeel-oz]

Hi Andy,

The image you posted doesn't show up on GitHub. You seem to be using MonthName in the measure, but MonthStart in the dimension. The function you use in the measure should match the dimension.

The future periods need to be added in a way that if you put your dimension and actual values (the measure part of the forecast expression) in a table you get one row for each month, will NULL values for the future months. The left join may not be doing what you expect. You could also use the forecasting calendar approach described in the documentation.