topic Re: Department wise future data using loops in qlikview in QlikView

Department wise future data using loops in qlikview

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Department wise future data using loops in qlikview

Anonymous — Wed, 22 Jun 2016 07:30:21 GMT

HI Thiru,

How you will calculate your prediction?

Regards!

Re: Department wise future data using loops in qlikview

Wed, 22 Jun 2016 07:34:28 GMT

Hi Manuel,

using 1% growth and 2% growth based on departments

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

please suggest me how to use loops?

Thiru

Re: Department wise future data using loops in qlikview

Anonymous — Wed, 22 Jun 2016 07:45:00 GMT

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!

Re: Department wise future data using loops in qlikview

Wed, 22 Jun 2016 07:56:08 GMT

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

Re: Department wise future data using loops in qlikview

Anonymous — Wed, 22 Jun 2016 08:04:19 GMT

Hi Thiru,

There are some functions to do it, see this:

Forecasting in Qlikview

Regards!

Re: Department wise future data using loops in qlikview

Kushal_Chawda — Wed, 22 Jun 2016 08:10:39 GMT

see this

Sales forecast | Qlik Community

Re: Department wise future data using loops in qlikview

Wed, 22 Jun 2016 09:34:53 GMT

Hi Exactly like this

Is you can provide the script it is very useful.

Thanks

Thiru

Re: Department wise future data using loops in qlikview

Kushal_Chawda — Wed, 22 Jun 2016 09:43:25 GMT

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

Re: Department wise future data using loops in qlikview

Wed, 22 Jun 2016 11:01:43 GMT

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

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

Re: Department wise future data using loops in qlikview

Mon, 27 Jun 2016 07:24:39 GMT

HI Kushal,

Any update for the below code?

Thiru