topic Re: Calculate with 2 date fields in QlikView

Calculate with 2 date fields

pauldamen — Fri, 06 Dec 2013 14:10:09 GMT

All,

I need to make a chart with a resource planning. I have 3 fields, start date, end date and fte. What I need is to make a chart that shows months and a line with the sum of FTE's.

But how the date is loaded, the fte is plotted on the startdate. How can I make the chart that shows the FTE over the months. So if the start date is jan 13 and the end date is dec 13 and the FTE is 1,0. It should show a bar every month for 1 fte.

Hope you guys can help.

Regards,

Paul

Re: Calculate with 2 date fields

datanibbler — Fri, 06 Dec 2013 14:18:04 GMT

Hi Paul,

from what I understand it seems you need to generate the missing data - the missing dates, that is, so that

=> out of those two records (jan13 and dec13, each with FTE=1)

=> you will generate every day inbetween, each with FTE=1

Right?

If so, try

LET vStartDatum = num(makedate((year(TODAY())), 1, 13));
LET vEndDatum = num(MakeDate((year(TODAY())),12,13));

Datumsliste:
LOAD date($(vStartDatum)+IterNo()-1) as Datumsfeld
AutoGenerate (1)
While $(vStartDatum)+IterNo()-1 <= $(vEndDatum);

(the basis of a master_calendar in all our apps)

=> You can then just join the FTE_value to every record in this date_list.

HTH

Best regards,

DataNibbler

Re: Calculate with 2 date fields

Gysbert_Wassenaar — Fri, 06 Dec 2013 16:01:26 GMT

Perhaps this blog post helps: Creating Reference Dates for Intervals. Your problems sounds very similar.

Re: Calculate with 2 date fields

pauldamen — Mon, 23 Dec 2013 09:22:09 GMT

Hi,

Thanks for the help, I created the calendar based on the start date.

But how can I can join the FTE's to every relevant date between the start and end date?

Regards,

Paul

Re: Calculate with 2 date fields

Nicole-Smith — Mon, 23 Dec 2013 15:54:10 GMT

You may want to look into IntervalMatch (you may need the extended syntax).