Creating one record per Month

[Anonymous]

Hey everyone,

I followed the approach defined by hic in his "Generating Missing Data" resource.

In the example above it is filling in the missing records per day.  However, I need to fill in the missing records per month not per day.

For example, FromDate = 1/1/2013 and ToDate = 1/1/2014 so I need 12 records one for each month (1/1/2013, 2/1/2013, 3/1/2013, etc).

I have used the below solution which seems to work but is not very straightforward.  Is there a more straightforward solution?

[#2]

Hi Michael, Can you please share the source data ?

[Anonymous]

Hey Dathu,

Not sure if I'm allowed to share the data even if it is scrambled, so I would rather not.  However, if you assume the source data is near identical to what Henric is using than it should be close enough. 

Generating Missing Data In QlikView

Again, it appears my solution is working but I tend to make things more complicated than they need to be and run into problems further into the development.

Thanks,
Mike

[#4 · Accepted solution]

Michael,

It seems clear. I would have coded differently the last WHERE clause, but it is certainly not  so good (it depends on the date you may have)

WHERE addmonths([Start Date], IterNo()-1) <= [End Date]

So I understand that the loop is done until the End Date. And no need to compute the MonthDiff.

Fabrice