How to fill missing months in data ?

[#1]

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.

[Gysbert_Wassenaar]

See this document: Generating Missing Data In QlikView

[sinanozdemir]

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.

Other useful links by Sinan:

Handling Metadata in QlikView

QlikView and Python Integration

QlikView Automation Series – Unzip & Load Excel Files

[#4]

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.

[#5]

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 ?