topic How to fill missing months in data ? in QlikView

How to fill missing months in data ?

Thu, 11 Feb 2016 16:44:21 GMT

Hi all,

How can i fill the gaps in the Month column?

Load

Product Month Price

A 01/01/2015 10

A 02/01/2015 20

A 05/01/2015 15

A 06/01/2015 17

When I bring this to bar chart how can I show all the months between minMonth and maxMonth.

i.e, March and April as 0's.

Without hard coding. Because I have some Many number of products. I came to know that it can be done using Master calendar.

But I am not able to use that in this case. Please help me out.

Thanks

Prashanth.

Re: How to fill missing months in data ?

Gysbert_Wassenaar — Thu, 11 Feb 2016 17:21:40 GMT

See this document: Generating Missing Data In QlikView

Re: How to fill missing months in data ?

sinanozdemir — Thu, 11 Feb 2016 17:43:45 GMT

Hi,

I usually follow the below approach if the dataset is not too big and I don't have time to edit the load script:

Define two variables - vMinMonth & vMaxMonth

And in the chart, create a calculated dimension by using ValueLoop() and plug in the above variables:

In the expression, enter the below:

And finally the chart:

However, this approach has its own flaws. I am attaching the qvw if you are interested.

Re: How to fill missing months in data ?

Fri, 12 Feb 2016 11:56:05 GMT

Hi Sinan,

Thanks for the reply.

I have to do it in Sense.

I have so many products to loop from and this need to be done in combo chart to shoe the bars and trend line.

If I can able to fill that gaps then the line will be straight and that's what exactly I wanted.

Re: How to fill missing months in data ?

Fri, 12 Feb 2016 13:09:00 GMT

Hi Wassenaar,

It's working with master calendar, but when I add the month column from master calendar table, when I use my original date column it's not working. I guess somewhere I need to use loop the Products with the date column. So how can do that ?