Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Can't figure out this simple problem:
DATES:
LOAD id, Date#(date) as date, status INLINE [
id, date, Area
A, 01/01/2018, 18
B, 02/02/2018, 25
B, 02/03/2018, 26
C, 03/03/2018, 31
D, 04/04/2018, 42
D,04/05/2018, 43
D, 04/05/2018, 44];
I'm trying to create an expression to:
- count (Area) where Date = maxDate for eg: for id 'D' there are three dates, trying to get count when date is max which is 04/05/2018. Same for id B where date is maxdate which is 05/03/2018. where date in format of MM/DD/YYYY.
Would appreaciate the help.
-Garry
what if the data is
DATES:
LOAD id, Date#(date) as date, status INLINE [
id, date, Area, State
A, 01/01/2018, 18, Open
B, 02/02/2018, 25 ,Closed
B, 02/03/2018, 26, Open
C, 03/03/2018, 31 ,Closed
D, 04/04/2018, 42, Open
D,04/05/2018, 43 ,Closed
D, 04/05/2018, 44,, Open ];
same requirement with state = closed.
Same requirement? as in the first requirement?
Yes same requirement.
Sum(Aggr(If(Max(TOTAL <id> date) = date, Sum(1), 0), id, date))
The above gives me for each ID what was the count of records with max(Date)
Requirement is to see the above with State is closed
Try this
Sum({<state = {'closed'}>} Aggr(If(Max({<state = {'closed'}>} TOTAL <id> date) = Only({<state = {'closed'}>} date), Sum({<state = {'closed'}>} 1), 0), id, date))
This works.
Thanks a lot!!
How to derive the max of above value as a Reference line?
RangeSum(
Sum(Aggr(If(Max(TOTAL <id> date) = date, Sum(1), 0), id, date))
-Above(Sum(Aggr(If(Max(TOTAL <id> date) = date, Sum(1), 0), id, date)))
)
for this?