topic Get Max Value between 2 Dates (per Day) in QlikView

Get Max Value between 2 Dates (per Day)

5abi0001 — Wed, 31 Jan 2018 13:20:07 GMT

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)

sunny_talwar — Wed, 31 Jan 2018 13:29:52 GMT

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)

5abi0001 — Wed, 31 Jan 2018 14:32:08 GMT

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)

sunny_talwar — Wed, 31 Jan 2018 14:35:06 GMT

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)

5abi0001 — Wed, 31 Jan 2018 14:41:43 GMT

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)

sunny_talwar — Wed, 31 Jan 2018 14:59:46 GMT

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

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

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

5abi0001 — Thu, 01 Feb 2018 09:44:16 GMT

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)

sunny_talwar — Thu, 01 Feb 2018 12:14:58 GMT

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)

5abi0001 — Thu, 01 Feb 2018 14:20:18 GMT

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

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

Mahamed_Qlik — Mon, 19 Sep 2022 07:43:44 GMT

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.