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.

5abi0001
New Contributor III

Get Max Value from a Field between two dates

Hey, i have a question. I have a table with values .. and two variables where i have two dates (a Startdate and an Enddate) - this date i can change everytime and now i want to now the max value from the field "duration" within this two dates. How can i do this? Im very lowskilled in scripting :-(

Thanks for every help!

1 Solution

Accepted Solutions
jasonmomoa
Contributor

Re: Get Max Value from a Field between two dates

No. You cannot use the num(date()) function in set analysis.

You need to create a new field in your script similar as

Calendar:

Load

  Date(tmpDate, 'MM/DD/YYYY')      as OpenDate,

  Num(Date(tmpDate, 'MM/DD/YYYY')) as [OpenDate Key]

Resident tmpCalendar;

The value of the new field will be close to 43345 (as an example). Your variables start and end date need to be converted as nums as well.

Then, you'll be able to use this new field in your expression as above.

Max({<[OpenDate Key]={">= $(StartDate) <= $(EndDate) "}>} Duration)

5 Replies
MVP
MVP

Re: Get Max Value from a Field between two dates

UI solution could be like:

Max({<Date={">=$(Startdate)<=$(Enddate)"}>}duration)

jasonmomoa
Contributor

Re: Get Max Value from a Field between two dates

Hi 5abi0001‌,

Sometimes working with Date formats is tricky. I consider that the best option to perform comparison operations is to work with numercial values.

I would convert the date values to numerical values using Num(Date([Date])) as [Date Key]. Then, you'll be able to successfully get the maximum value using the following expression:

Max({<[Date Key]={">= $(vStartDateKey) <=) $(vEndDateKey) "}>} [Field Name])

Regards,

5abi0001
New Contributor III

Re: Get Max Value from a Field between two dates

If i do so, i got an Error (Error in set modifier expression).  The Dates in the variables StartDate and EndDate are already in Numberic Format. The Date of the Field (OpenDate) is in the Format 'YYYY-MM-DD'.

According to ur answer the formula should be the following:

=Max({<Num(Date(OpenDate))={">= $(StartDate) <=) $(EndDate) "}>} Duration)

E: I got it! Thank u both! :-)

jasonmomoa
Contributor

Re: Get Max Value from a Field between two dates

No. You cannot use the num(date()) function in set analysis.

You need to create a new field in your script similar as

Calendar:

Load

  Date(tmpDate, 'MM/DD/YYYY')      as OpenDate,

  Num(Date(tmpDate, 'MM/DD/YYYY')) as [OpenDate Key]

Resident tmpCalendar;

The value of the new field will be close to 43345 (as an example). Your variables start and end date need to be converted as nums as well.

Then, you'll be able to use this new field in your expression as above.

Max({<[OpenDate Key]={">= $(StartDate) <= $(EndDate) "}>} Duration)

5abi0001
New Contributor III

Re: Get Max Value from a Field between two dates

This works fine .. thank u :-)

I have a last question .. in my diagram i show per Day how much problems were created (For example on 2017-11-11 74 problems were created). Now i want to know within the StartDate and the EndDate the Max Value of Problems per Day.

For example: Within the period of time 2018-01-01 - 2018-01-15 i want to know the Max Problems per Day.

My tables looks like:

OpenDate          ProblemID          Duration

2018-01-01         238848              23

2018-01-01         923333              184

2018-01-12         938489               94

For example the solution would be now 2!

I hope u can understand what i want to say. :-)