Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using loops how to create forecast data?

Hi

I want to create forecast data using loops?

Department,Region., Sales-amount, 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


Thanks

thiru

0 Replies