Using loops how to create forecast data?

Wed, 25 Nov 2020 16:16:04 GMT

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));

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

topic Using loops how to create forecast data? in QlikView

Using loops how to create forecast data?