Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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. 🙂
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?
May be this
Max(Aggr(Count(DISTINCT ProblemID), OpenDate, ProblemID))
or this in the chart
Max(TOTAL Aggr(Count(DISTINCT ProblemID), OpenDate, ProblemID))
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!
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))
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))
How about this (removed ProblemID from the aggr() function)
=Max({<OpenDate= {">=$(StartDate)<=$(EndDate)"}>} Aggr(Count({<OpenDate= {">=$(StartDate)<=$(EndDate)"}>} DISTINCT ProblemID), OpenDate))
Hm no - it does not work. 😞 .. I uploaded u a file - so maybe u understand how it looks like.
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?
U are a hero! Thank u .. it works fine in my rel qvw! 🙂
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.