topic Re: generating missing months with zero sales for full year in QlikView

generating missing months with zero sales for full year

Anonymous — Wed, 25 Oct 2017 01:28:08 GMT

I have a requirement to generate records for missing months. The missing records should be populated with 0 Sales.

The link - generating missing months is very similar to what I need but I couldn't figure out how to use iterno() for my situation. I need to fill the amount for the full Year. Eg:

Source Data:

Acnt	Month	Sales
A1	Jan	56
A1	Feb	48
A1	Nov	49
A2	Jan	45
A2	Feb	34
A2	Mar	23
A2	Apr	32
A2	May	46
A2	Jun	78
A2	Jul	35
A2	Aug	54
A2	Sep	97
A2	Oct	66
A2	Nov	77
A2	Dec	88
A2	Jan	70

This is how my resulting data set should look like:

Acnt	Month	Sales
A1	Jan	56
A1	Feb	48
A1	Nov	49
A1	Mar	0
A1	Apr	0
A1	May	0
A1	Jun	0
A1	Jul	0
A1	Aug	0
A1	Sep	0
A1	Oct	0
A1	Dec	0
A2	Jan	45
A2	Feb	34
A2	Mar	23
A2	Apr	32
A2	May	46
A2	Jun	78
A2	Jul	35
A2	Aug	54
A2	Sep	97
A2	Oct	66
A2	Nov	77
A2	Dec	88
A2	Jan	70

Appreciate all your help.

Thanks

Samy

Re: generating missing months with zero sales for full year

Anil_Babu_Samineni — Wed, 25 Oct 2017 04:12:13 GMT

Are you expecting in script / Front end?

Re: generating missing months with zero sales for full year

Anonymous — Wed, 25 Oct 2017 04:13:44 GMT

in the script,, thank you!

Re: generating missing months with zero sales for full year

Anil_Babu_Samineni — Wed, 25 Oct 2017 04:15:41 GMT

Have you seen this?

Generating Missing Data In QlikView

Re: generating missing months with zero sales for full year

Anonymous — Wed, 25 Oct 2017 04:19:46 GMT

Used following script to generate Missing Month against its values.

Temp_Date_Range:

LOAD

num(Min(DATE("RegistrationDate"))) as MinDate,

num(Max(DATE("RegistrationDate"))) as MaxDate

Resident SourceData_TB;

LET vMinDate=Peek('MinDate',0,'Temp_Date_Range');

LET vMaxDate=Peek('MaxDate',0,'Temp_Date_Range');

drop table Temp_Date_Range;

MasterCalender:

LOAD

Date(Temp_Date) as "Key",

Year(Temp_Date) as Year,

Month(Temp_Date) as Month;

LOAD DISTINCT

$(vMinDate) +IterNo()-1 as Temp_Date

AutoGenerate(1)

while IterNo()+$(vMinDate)-1<=$(vMaxDate);

Regards

Sahadev

Re: generating missing months with zero sales for full year

Anil_Babu_Samineni — Wed, 25 Oct 2017 04:24:17 GMT

Can you share the QVW file where you working?