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
Or this
Sum(Aggr(If(Max(TOTAL <id> date) = date, Sum(1), 0), id, date))
Try this
Sum({<state = {'closed'}>} Aggr(If(Max({<state = {'closed'}>} TOTAL <id> date) = Only({<state = {'closed'}>} date), Sum({<state = {'closed'}>} 1), 0), id, date))
Count({$<Date={"=$(=Max(Date))"}>} Area)
May be this
Sum(Aggr(If(Max(TOTAL <id> date) = date, 1, 0), id, date))
Or this
Sum(Aggr(If(Max(TOTAL <id> date) = date, Sum(1), 0), id, date))
Hey,
the above worked. Another question, if I am looking the change in records.
For example:
now with the above, I get this
id,date, Records count
A, 01/01/2018, 18
B,02/03/2018,26
C, 03/03/2018,31
D,04/05/2018,44
i would like to see this
A, 01/01/2018, 18
B,02/03/2018,(26 - 18)
C, 03/03/2018,(31 - 26)
D,04/05/2018,(44 - 31)
How can this be achieved?
This
RangeSum(
Sum(count), -Above(Sum(count))
)
where to insert set identifier '1'?
not sure what you mean?
for the above expression, I dont need the chart to change with any date selection
Oh okay... may be this
RangeSum(
Sum({1} count), -Above(Sum({1} count))
)