Get Max Value between 2 Dates (per Day)

[5abi0001]

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. 🙂

[sunny_talwar]

May be this

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

or this in the chart

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

[5abi0001]

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]

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]

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]

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

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

[5abi0001]

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

[sunny_talwar · Accepted solution]

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?

[5abi0001]

U are a hero! Thank u .. it works fine in my rel qvw! 🙂

[Mahamed_Qlik]

Hi 

I have something matching requirement but little different.

I have two dates ( CalDate and PosDate)

if I select my CalDate as 09/19/2018 then below records are populating which is correct:

ID          DEPT           DEPT_TYPE     PosDate       Count

100      Account      AB                      06/01/2021   1

100      Finance       AB                      03/29/2008   0

100      Finance      AB                       12/25/2011   0

100      Finance      AB                        01/01/2018  0

Now here you can see ID-100 was working in multiple DEPT with different PosDate and as per current logic 
its calculating count as 1 for max of total PosDate.

Expetation is when user select Caldate as 09/19/2018  then  during this period ID was working in Finanance DEPT and its max posDate is 01/01/2018 so this record needs to shown in count as 1.

expected result:

ID          DEPT           DEPT_TYPE     PosDate       Count

100      Account      AB                      06/01/2021   -                       <---- this record can be null or removed as Caldate is                                                                                                                                       backdated date selected

100      Finance       AB                      03/29/2008   0

100      Finance      AB                       12/25/2011   0

100      Finance      AB                        01/01/2018  1                <--- this needs to shown only for backdated calDate with                                                                                                                           max posDate

How to achieve this in set analysis.