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
Not applicable

Get Max Value between 2 Dates (per Day)

Hey,

i already opened a discussion (Re: Get Max Value from a Field between two dates). I got the answer, but now i have a second problem.

Nn 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 (both are variables, which can be modified) the Max Value of Problems per Day. In my Table I have data which show the problem, when it occours.

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

2018-01-12         284928              83

2018-01-12         283829              89

2018-01-14         398200              12    

For example the solution would be now 3!

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

1 Solution

Accepted Solutions
sunny_talwar
Not applicable

Re: Get Max Value between 2 Dates (per Day)

I don't know if this is an issue in your real qvw or not, but your sample's OpenDate is a timestamp. In order to convert it into a date, I created another field like this

Date(Floor(OpenDate)) as OpenDateFloored

Now try this expression

=Max({<OpenDate= {">=$(StartDate)<=$(EndDate)"}>} Aggr(Count({<OpenDate= {">=$(StartDate)<=$(EndDate)"}>} DISTINCT ProblemID), OpenDateFloored))

This gives me 200, is this the number you were after?

Capture.PNG

8 Replies
sunny_talwar
Not applicable

Re: Get Max Value between 2 Dates (per Day)

May be this

Max(Aggr(Count(DISTINCT ProblemID), OpenDate, ProblemID))

or this in the chart

Max(TOTAL Aggr(Count(DISTINCT ProblemID), OpenDate, ProblemID))

5abi0001
Not applicable

Re: Get Max Value between 2 Dates (per Day)

Maybe this would be a good solution .. but i want the Max of Problems within the StartDate and EndDate.

I Habe a OpenDate were the problem occured. 2 variables StartDate and EndDate - with these two variables i can limit the OpenDate -> And now i want to now the max amount of problems which occured between StartDate and EndDate.

The variable and OpenDate is in a numberic format!

sunny_talwar
Not applicable

Re: Get Max Value between 2 Dates (per Day)

Well, I thought you would know that you need to continue using any set analysis that you had from the previous discussion... try this

Max({<[OpenDate Key] = {">=$(StartDate)<=$(EndDate)"}>} Aggr(Count({<[OpenDate Key] = {">=$(StartDate)<=$(EndDate)"}>} DISTINCT ProblemID), OpenDate, ProblemID))

5abi0001
Not applicable

Re: Get Max Value between 2 Dates (per Day)

First of all thank u that u try to help me :-)

I tried it out .. but it does not work. It shows me 1 .. this is defitintiv not correct! OpenDate - in which format should this be? Because now i made OpenDate in numberic format (trough Documentsettings).

=Max({<OpenDate= {">=$(StartDate)<=$(EndDate)"}>} Aggr(Count({<OpenDate= {">=$(StartDate)<=$(EndDate)"}>} DISTINCT ProblemID), OpenDate, ProblemID))

sunny_talwar
Not applicable

Re: Get Max Value between 2 Dates (per Day)

How about this (removed ProblemID from the aggr() function)

=Max({<OpenDate= {">=$(StartDate)<=$(EndDate)"}>} Aggr(Count({<OpenDate= {">=$(StartDate)<=$(EndDate)"}>} DISTINCT ProblemID), OpenDate))

5abi0001
Not applicable

Re: Get Max Value between 2 Dates (per Day)

Hm no - it does not work. :-( .. I uploaded u a file - so maybe u understand how it looks like.

sunny_talwar
Not applicable

Re: Get Max Value between 2 Dates (per Day)

I don't know if this is an issue in your real qvw or not, but your sample's OpenDate is a timestamp. In order to convert it into a date, I created another field like this

Date(Floor(OpenDate)) as OpenDateFloored

Now try this expression

=Max({<OpenDate= {">=$(StartDate)<=$(EndDate)"}>} Aggr(Count({<OpenDate= {">=$(StartDate)<=$(EndDate)"}>} DISTINCT ProblemID), OpenDateFloored))

This gives me 200, is this the number you were after?

Capture.PNG

5abi0001
Not applicable

Re: Get Max Value between 2 Dates (per Day)

U are a hero! Thank u .. it works fine in my rel qvw! :-)