Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Show minimum date greater than today....

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:

MilestoneCalendarDate
Start1/1/15
Jump2/2/15
Jump3/3/15
Jump4/4/15
Kick5/5/15
Kick6/6/15
Kick7/7/15
Kick8/8/15
Kick9/9/15
Spin10/10/15
Spin11/11/15
Spin12/12/15
Finish1/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

1 Solution

Accepted Solutions
morganaaron
Not applicable

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

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

14 Replies
jontydkpi
Not applicable

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

Perhaps this:

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
morganaaron
Not applicable

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

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

marcus_sommer
Not applicable

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

You could try something like this:

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

- Marcus

Not applicable

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

Jonathan,

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

Thanks,
Vince

Not applicable

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

Hey Aaron,

This still is not showing the Kick milestone.

All other items are showing correctly.

Thanks,
Vince

Not applicable

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

Marcus,

I am getting a "No data to display message"

Thanks,
Vince

marcus_sommer
Not applicable

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

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

- Marcus

morganaaron
Not applicable

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

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
)

)

)

)

Not applicable

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

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

I will keep playing with it