8 Replies Latest reply: Feb 1, 2018 9:20 AM by Prince Sabi

# 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. :-)

• ###### 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))

• ###### 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!

• ###### 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))

• ###### 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))

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

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

• ###### 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.

• ###### 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?

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

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