topic Re: Count of Max date in App Development

Count of Max date

garry107 — Wed, 25 Jul 2018 19:25:02 GMT

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

Re: Count of Max date

dwforest — Wed, 25 Jul 2018 20:40:12 GMT

Count({$<Date={"=$(=Max(Date))"}>} Area)

Re: Count of Max date

sunny_talwar — Wed, 25 Jul 2018 22:31:00 GMT

May be this

Sum(Aggr(If(Max(TOTAL <id> date) = date, 1, 0), id, date))

Re: Count of Max date

sunny_talwar — Wed, 25 Jul 2018 22:31:24 GMT

Or this

Sum(Aggr(If(Max(TOTAL <id> date) = date, Sum(1), 0), id, date))

Re: Count of Max date

garry107 — Thu, 26 Jul 2018 14:02:57 GMT

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?

Re: Count of Max date

sunny_talwar — Thu, 26 Jul 2018 14:04:05 GMT

This

RangeSum(

Sum(count), -Above(Sum(count))

)

Re: Count of Max date

garry107 — Thu, 26 Jul 2018 14:12:26 GMT

where to insert set identifier '1'?

Re: Count of Max date

sunny_talwar — Thu, 26 Jul 2018 14:19:31 GMT

not sure what you mean?

Re: Count of Max date

garry107 — Thu, 26 Jul 2018 14:22:54 GMT

for the above expression, I dont need the chart to change with any date selection

Re: Count of Max date

sunny_talwar — Thu, 26 Jul 2018 14:23:32 GMT

Oh okay... may be this

RangeSum(

Sum({1} count), -Above(Sum({1} count))

)

Re: Count of Max date

garry107 — Thu, 26 Jul 2018 17:49:07 GMT

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.

Re: Count of Max date

sunny_talwar — Thu, 26 Jul 2018 18:04:16 GMT

Same requirement? as in the first requirement?

Re: Count of Max date

garry107 — Thu, 26 Jul 2018 18:09:25 GMT

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

Re: Count of Max date

sunny_talwar — Thu, 26 Jul 2018 18:14:46 GMT

Try this

Sum({<state = {'closed'}>} Aggr(If(Max({<state = {'closed'}>} TOTAL <id> date) = Only({<state = {'closed'}>} date), Sum({<state = {'closed'}>} 1), 0), id, date))

Re: Count of Max date

garry107 — Thu, 26 Jul 2018 18:52:57 GMT

This works.

Thanks a lot!!

Re: Count of Max date

garry107 — Tue, 31 Jul 2018 17:44:19 GMT

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?