Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

How to fill missing months in data ?

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.

4 Replies

Re: How to fill missing months in data ?

See this document: Generating Missing Data In QlikView


talk is cheap, supply exceeds demand
sinanozdemir
Valued Contributor III

Re: How to fill missing months in data ?

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

Capture.PNG

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

Capture.PNG

In the expression, enter the below:

Capture.PNG

And finally the chart:

Capture.PNG

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

Not applicable

Re: How to fill missing months in data ?

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.

trend1.png

Not applicable

Re: How to fill missing months in data ?

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 ?

Community Browser