topic Re: Show minimum date greater than today.... in QlikView

Show minimum date greater than today....

Tue, 28 Jul 2015 13:00:21 GMT

Morning,

I am trying to find a way to display milestones on a chart- the problem is that I have repetitively named milestones and I have to tell the chart which to display. Here are the characteristics of an if expression I want to create:

If there is only one milestone with a particular name show the date
If there are more than one milestone with a particular name and they all happen in the future (> today) show the minimum date
If there are more than one milestone with a particular name and they all happen in the past (< today) show the maximum date
If there are more than one milestone with a particular name and some happen in the past and some in the future show the next occurring date

The data would look something like this if today = 7/28/15:

Milestone	CalendarDate
*Start*	*1/1/15*
Jump	2/2/15
Jump	3/3/15
*Jump*	*4/4/15*
Kick	5/5/15
Kick	6/6/15
Kick	7/7/15
*Kick*	*8/8/15*
Kick	9/9/15
*Spin*	*10/10/15*
Spin	11/11/15
Spin	12/12/15
*Finish*	*1/1/16*

The red highlights are the milestones I want to show in the chart:

The dimension on the chart is - Milestone and

I have started the following expression and everything is working so far, except the next (Spin in the example above), which I am trying to perform as set analysis in the last nest of the if statement:

if (count ([Milestone])=1, CalendarDate,

if (count ([Milestone])>1 and min(CalendarDate)-today()>=0, min(CalendarDate),

if (count ([Milestone])>1 and max(CalendarDate)-today()<=0, max(CalendarDate),

if (count ([Milestone])>1 and (min(CalendarDate)-today()>=0 and max(CalendarDate)-today()<=0),

Only({<CalendarDate= {">=$(= today())"}>} CalendarDate)

)

Any help or guidance would be greatly appreciated.

Thanks,
Vince

Re: Show minimum date greater than today....

jonathandienst — Tue, 28 Jul 2015 13:19:37 GMT

Perhaps this:

If(Count(CalendarDate) = 1, CalendarDate, Min({<CalendarDate = {">=$(=Today())"}>} CalendarDate))

Re: Show minimum date greater than today....

morganaaron — Tue, 28 Jul 2015 13:21:18 GMT

Hi Vince,

Just change your last expression to Min() instead of Only() and it should work - though you could remove your last If() statement as this should be the only available condition left and so you can have it as your 'false' part of the previous if statement.

Aaron

Re: Show minimum date greater than today....

marcus_sommer — Tue, 28 Jul 2015 13:23:10 GMT

You could try something like this:

firstsortedvalue({< [CalendarDate] = {"<$(=date(today(),'M/D/YY'))"}>} [CalendarDate], -[CalendarDate])

- Marcus

Re: Show minimum date greater than today....

Tue, 28 Jul 2015 13:25:37 GMT

Jonathan,

No items or milestones show on the chart with this statement.

Thanks,
Vince

Re: Show minimum date greater than today....

Tue, 28 Jul 2015 13:26:59 GMT

Hey Aaron,

This still is not showing the Kick milestone.

All other items are showing correctly.

Thanks,
Vince

Re: Show minimum date greater than today....

Tue, 28 Jul 2015 13:28:18 GMT

Marcus,

I am getting a "No data to display message"

Thanks,
Vince

Re: Show minimum date greater than today....

marcus_sommer — Tue, 28 Jul 2015 13:34:07 GMT

Is [CalendarDate] numeric - it's left-aligned? For each calculation or matching on date-fields the fields needed to be numeric.

- Marcus

Re: Show minimum date greater than today....

morganaaron — Tue, 28 Jul 2015 13:49:36 GMT

Hi Vince,

Just tested myself and it worked - the exact expression I used is:

if (count ([Milestone])=1, CalendarDate,

if (count ([Milestone])>1 and min(CalendarDate)-today()>=0, min(CalendarDate),
if (count ([Milestone])>1 and max(CalendarDate)-today()<=0, max(CalendarDate),
Min({<CalendarDate= {">=$(= today())"}>} CalendarDate)

)

Re: Show minimum date greater than today....

Tue, 28 Jul 2015 14:52:56 GMT

Unfortunately I am not seeing that result - the Spin is disappearing from the data set.

I will keep playing with it

Re: Show minimum date greater than today....

morganaaron — Tue, 28 Jul 2015 15:02:36 GMT

As other people mentioned, it may have to do with the format of your dates.

I've attached my copy so you can see what it looks like with clean data from an inline load.

Re: Show minimum date greater than today....

Tue, 28 Jul 2015 15:08:59 GMT

The date format of the field is a four digit code

Re: Show minimum date greater than today....

Tue, 28 Jul 2015 17:01:32 GMT

I have modified the values to:

date(CalendarDate,'M/D/YYYY') so that it matches format.

Now the Milestone shows (first time ever) - but unfortunately it is making it today's date, not the next.

Re: Show minimum date greater than today....

Tue, 28 Jul 2015 17:35:48 GMT

WOW - it worked - i didn't need to modify my calendar date -

I needed to Floor(today()) to make it numeric.

Thank you soooooo much for your help

final script:

if (count ([Milestone])=1, CalendarDate,

)

Re: Show minimum date greater than today....

Tue, 28 Jul 2015 17:36:34 GMT

Thank you for your guidance - the matching of field types for comparison was ultimately the issue