topic Re: average sales using set analysis and generate the forecast data? in QlikView

average sales using set analysis and generate the forecast data?

Wed, 25 Nov 2020 16:16:04 GMT

I have scenario,

I have database fileds like below

Date,

Region,

Location,

Sales amount,

i have data upto last month i.e.2016-06-0100:00:00.000

for every region and location there is one record,i need to generate the forecast data from 2016-07-0100:00:00.000 to another 10 years

and salesamount is salesamount+salesamount*(avg of last 24 months)

Date	Region	Location	SalesAmount
2016-06-0100:00:00.000	INDIA	Bangalore	4589
2016-06-0100:00:00.000	INDIA	Delhi	5897
2016-06-0100:00:00.000	US	Nuyork	6965
2016-06-0100:00:00.000	UK	London	5879
2016-07-0100:00:00.000	INDIA	Bangalore
2016-07-0100:00:00.000	INDIA	Delhi
2016-07-0100:00:00.000	US	Nuyork
2016-07-0100:00:00.000	UK	London
2016-08-0100:00:00.000	INDIA	Bangalore
2016-08-0100:00:00.000	INDIA	Delhi
2016-08-0100:00:00.000	US	Nuyork
2016-08-0100:00:00.000	UK	London
2016-09-0100:00:00.000	INDIA	Bangalore
2016-09-0100:00:00.000	INDIA	Delhi
2016-09-0100:00:00.000	US	Nuyork
2016-09-0100:00:00.000	UK	London

how to get this please suggest.

Thanks

Thiru

Re: average sales using set analysis and generate the forecast data?

TKendrick20 — Wed, 29 Jun 2016 14:14:37 GMT

Cool, this is something I have been looking into for a dashboard I'm building. My strategy was actually to compile the forecasted data in the data load. So, I would load in all of the sales data up until the most recent data that I have available, then I would LOOP over a CONCATENATE statement to add in forecasted data based on some equation.

Does this make sense? You may need to rework that Date field you have so it's easier to use the various DATE functions in the data load. Let me know if anyone else has any good ideas for this scenario, I am very interested.

Re: average sales using set analysis and generate the forecast data?

Thu, 30 Jun 2016 04:50:54 GMT

I am also doing same but how to get the 24 months average?

in the equation.

Thanks

Thiru

Re: average sales using set analysis and generate the forecast data?

TKendrick20 — Thu, 30 Jun 2016 13:03:50 GMT

Matching linest_m and linest_b function to auto-generated trendline

Using the LINEST_B and LINEST_M functions of Qlik Sense may be another way to forecast based on trendlines. As for the 24 month average, you could do something similar with the AGGR function to calculate the average and apply it to the dates in the future.

Re: average sales using set analysis and generate the forecast data?

Fri, 01 Jul 2016 11:03:57 GMT

Hi Tim,

I need to generate data for the forecast,because i need to provide that column as input column as input column for end user.

Thanks

Thiru