Populate missing months

[#1]

Hi All,

I have a table with data like below,  in the table for the unit 7511 I am not getting some of the months data because of I didn't have sales for that unit on the messing month. however I would like to show ,unit number, missing month,  '0' in the Sold column how do I get this, can any one help me on this.

Actual:  

Unit	Month	Actual
7511	Sep 2014	139232
7511	Nov 2014	17023
7511	Dec 2014	7177
7511	Jan 2015	6827
7511	Feb 2015	2738
7511	Mar 2015	13245
7511	Apr 2015	12523
7511	Jun 2015	20164
7511	Jul 2015	5890
7511	Aug 2015	5071
7511	Sep 2015	11891
7511	Oct 2015	2757
7511	Nov 2015	7036
7511	Dec 2015	4195
7511	Jan 2016	6372

Expected

Unit	Month	Actual
7511	Sep 2014	139232
7511	Oct 2014	0
7511	Nov 2014	17023
7511	Dec 2014	7177
7511	Jan 2015	6827
7511	Feb 2015	2738
7511	Mar 2015	13245
7511	Apr 2015	12523
7511	May 2015	0
7511	Jun 2015	20164
7511	Jul 2015	5890
7511	Aug 2015	5071
7511	Sep 2015	11891
7511	Oct 2015	2757
7511	Nov 2015	7036
7511	Dec 2015	4195
7511	Jan 2016	6372

[swuehl]

Generating Missing Data In QlikView

[sunny_talwar]

Do you have a Master Calendar in your application? If not, I would suggest creating a master calendar with continuous date from min date in your database to the max date in your database. Once you do that, you should be able to see all Months. For a more precise help, would you be able to share a sample?

[#4]

Hi john,

Please find the attached app for what you asked for. You can refine it further according to your requirement.

Hope it helps ..

Regards,

Lokeshwari

[Colin-Albert]

By default QlikView supresses zero rows from charts.

Can you check that the chart Properties, Presentation tab has Supress Zero values and Suppress Missing boxes not ticked.

If this does not show the values, then you will need to add a master calendar linked to your transaction dates so all dates exist in your data model.

[#6]

Hi Lokeshwari ,

thanks for the response, I tried the way you are using however in my model I have 100,000 units , each unit has minimum date and maximum date also there are some missing dates as well so I have followed your steps.

I have taken the dates from 2001 onwards because of this script my application becomes huge. almost 250 MB.

is there any other way.

[#7]

Hi Colin Albert

your points are taken, also I am providing some sample data which will let you know the problem I am facing,

I have five fields Unit, Date ,MonthYear ,Months, Actual, Accumulated Value.

here some of the Units  doesn't have  dates for some of the moths, however I need the missing month dates also, for that dates I need previous accumulated values. I tried the above options however  I am getting performance issues here taking more time to reload the application.

could you please guide me on this.

[#8]

Hi Sunny Please find the Sample data , in the sample data I am providing

Unit, Date, MonthYear, Months, Actual , Accumulated Value as fields

I need Continuos Dates for the Unit also need accumulated value for every date , if Accumulated value is not there for any of the date it should pick the previous date value.

[#9]

Hi Stephen,

I have read the document , and tried the approaches ,however I didn't get the right results, when I ran my machine , my system is not responding.

I am taking data from 2005 on wards, I have 45000+ units , when I try to generate dates for each unit from 2005 to till date I am getting this issue.

My requirement is

I have units and its service dates , I need to show the Accumulated value for each month weather the dates are the are not if date is there corresponding accumulated value I need to show, if not there pick previous date accumulated value for that date.

Could you please help me on this. I have attached sample data also.

[PradeepReddy]

are you looking for something like this. see the attachment...