Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

gatluri107
New Contributor

Count of Max date

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

1 Solution

Accepted Solutions

Re: Count of Max date

Or this

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

15 Replies
dwforest
Valued Contributor

Re: Count of Max date

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

Re: Count of Max date

May be this

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

Re: Count of Max date

Or this

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

gatluri107
New Contributor

Re: Count of Max 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?

Re: Count of Max date

This

RangeSum(

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

)

gatluri107
New Contributor

Re: Count of Max date

where to insert set identifier '1'?

Re: Count of Max date

not sure what you mean?

gatluri107
New Contributor

Re: Count of Max date

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

Re: Count of Max date

Oh okay... may be this

RangeSum(

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

)

Community Browser