Department wise future data using loops in qlikview

[#1]

HI I have scenario,

I have data like below

Date,          Department,    salesAmount

2012/01/01    Retail                54688

2012/01/01      sales                568489

.

.

.

.

.

2016/06/01    agriculturl              54646476

So here Number of departments are around 20 and for this i need add future values(prediction) for

onemonth /one department(example 2017 12 monthes*20 deparments)  around 240 records

In qlikview is there any logic using loops?

Please do the needful

Thanks

Thiru

[Anonymous]

HI Thiru,

How you will calculate your prediction?

Regards!

[#3]

Hi Manuel,

using 1% growth and 2% growth based on departments

like

2017/01/01  sales     salesamount*salesamout(0.1)    like that

please suggest me how to use loops?

Thiru

[Anonymous]

Hi thiru,

See this for loops on QlikView: https://community.qlik.com/blogs/qlikviewdesignblog/2013/09/02/loops-in-the-script

You will calculate it only for 2016 or for average or years?

Regards!

[#5]

HI

avg(all years) upto 2016 and based on that i need to populate 2017 to 2025

Is there any functions available to show forecasting analysis instead of populating data?

Thiru

[Anonymous]

Hi Thiru,

There are some functions to do it, see this:

Forecasting in Qlikview

Regards!

[Kushal_Chawda]

see this

Sales forecast | Qlik Community

[#8]

Hi Exactly like this

Is you can provide the script it is very useful.

Thanks

Thiru

[Kushal_Chawda]

I have attached the application there in reply you can check that

[#10]

Hi Kushal,

I have implemented same but not able to get the solution,

I have

Department,Region.Salesamount,Date are the database fields

so i need to populate future dates of these four fields,able to populate

Department,Date ,flag

not able to populate Region,Salesamount

This is my code

-----------------------

CER:

LOAD DISTINCT

  addmonths(Max(YearMonth),240) as MaxDate,

  max(YearMonth) as MaDate,

  min(YearMonth) as MinDate

  FROM

  $(FDirectory)\SALES.qvd(qvd);

LET CERMinDate = Num(Peek('MaDate', 0, 'CER'));

LET CERMaxDate = Num(Peek('MaxDate', 0, 'CER'));

SalesAmountDATA:

LOAD Department,

     Region,

     SalesAmount, 

     YearMonth

FROM

$(FDirectory)SALES.qvd(qvd);

let vCount = FieldValueCount('Department');

for i=1 to $(vCount)

let vItem = FieldValue('Department',$(i));

Concatenate (SalesAmountDATA)

LOAD '$(vItem)' as Department,

Date(AddMonths($(CERMinDate),IterNo()-1)) AS YearMonth

AUTOGENERATE 1  WHILE Date(AddMonths($(CERMinDate),IterNo()-1))<= ($(CERMaxDate));

NEXT

Flag:

NoConcatenate

LOAD *,

if(floor(YearMonth)<= Floor(Today()),'AsOn','Forecast') as Flag

Resident SalesAmountDATA;

DROP Table SalesAmountDATA;

DROP Table CER;

Left Join

LOAD YearMonth,

Department,

Region,

AVG(SalesAmount) as SalesAmount

Resident Flag

where Flag='AsOn'

Group by YearMonth,Department,Region;

New:

LOAD *,

if(Flag='AsOn',Department, if(Flag='Forecast', SalesAmount)) as FinalValue

Resident Flag;

DROP Table Flag;

please do the needful

Thiru