topic Re: Expression/ Difference Between Dates in QlikView

Expression/ Difference Between Dates

Sun, 19 Jan 2014 10:48:42 GMT

Hi!

I'm trying to create 3 graphics with the average of the number of days between two dates within the space of:

- Graphic 1/Expression 1: from 01.01.2013 to 31.12.2013;

- Graphic 2/Expression 2: From 01.06.2013 to 30.09.2013;

- Graphic 3/Expression 3: From 16.10.2012 to 10.02.2013;

I make an example of what I'm trying to calculate

Expression 1 - I calcutate only days between this interval from 01.01.2013 to 31.12.2013:

- When my start date is 28.10.2012 and my end date is 31.03.2013, the difference is 89 days (01.03.13 - 31.03.13);

- When my start date is 08.09.2013 and my end date is 16.01.2014 the difference is 114 days (08.09.13 - 31.12.13);

- When my start date is 22.09.2002 and my end date is blank, the difference is 365 days (01.01.13 - 31.12.13);

Expression2 - I calcutate only days between this interval from 01.10.2012 to 28.02.2013:

- When my start date is 28.10.2012 and my end date is 31.03.2013, the difference is 123 days (28.10.12 - 28.02.13);

- When my start date is 16.02.2013 and my end date is 16.03.2014 the difference is 12 days (16.02.13 - 28.02.13);

- When my start date is 22.09.2002 and my end date is blank, the difference is 150 days (01.10.12 - 28.02.13);

You can find an example attached.

Do you think it's possible to do?

Any suggestions greatly appreciated.

Andrea

Re: Expression/ Difference Between Dates

swuehl — Sun, 19 Jan 2014 12:36:08 GMT

You can start with something like

=rangemax(rangemin(makedate(2013,12,31),[End Date])-rangemax(makedate(2013,1,1),[Start Date]) ,0)

where the makedate() functions are defining your reference date intervals (Jan 1st, 2013 to Dec 31, 2013 in this sample). You may need to add 1 (if you want to include the start date in your calculation).

You can use aggregation functions like avg() around (otherwise this expression will only work correctly with unambiguous start and end dates, i.e. selections been made).

Hope this helps,

Stefan

Re: Expression/ Difference Between Dates

Sun, 19 Jan 2014 14:16:43 GMT

thank you so much for your help!

Andrea