Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

anson_kong
New Contributor II

Help: maximum from each count

Dear all,

I would like to set the formula to get the maximum from each count as below.

Result

TypeEachCount
ABC13

can anyone share the idea?

Thank you so much

Table column 

Type

(database column)

Order Date

(database column)

EachCount

(it is calculated measure, not in the database)

ABC29-Sep-201713
ABC19-Oct-201712
ABC03-Oct-201711
ABC21-Oct-201710
ABC10-Oct-20179
ABC12-Oct-20179
ABC17-Oct-20179
ABC20-Oct-20179
ABC23-Oct-20179

1 Solution

Accepted Solutions

Re: Help: maximum from each count

What is the expression for Count?

Try this

Dimension

Type

Expression

Max(Aggr(YourCountExpressionHere, Type, [Order Date]))

If you want this where both Type and Order Date are dimensions, then try this

Max(TOTAL <Type> Aggr(YourCountExpressionHere, Type, [Order Date]))

8 Replies
Chanty4u
Esteemed Contributor III

Re: Help: maximum from each count

how the count is derived?   how you are getting 13 and all that numbers?

OmarBenSalem
Esteemed Contributor

Re: Help: maximum from each count

max(total <Type> YourCount)

Chanty4u
Esteemed Contributor III

Re: Help: maximum from each count

you can try

=firstsortedvalue(type,-Eachcount)

Re: Help: maximum from each count

What is the expression for Count?

Try this

Dimension

Type

Expression

Max(Aggr(YourCountExpressionHere, Type, [Order Date]))

If you want this where both Type and Order Date are dimensions, then try this

Max(TOTAL <Type> Aggr(YourCountExpressionHere, Type, [Order Date]))

anson_kong
New Contributor II

Re: Help: maximum from each count

Thank you all,

I forget post the expression of my count.

anson_kong
New Contributor II

Re: Help: maximum from each count

Dear all,

I don't know how to modified the expression using Aggr () you provided to me.

here is my explanation, there are number transactions in order date by ID by order date.

I need to find the maximum unique ID exists in the particular period by Order Date.

in the below table, the maximum ID by type exists in 21-Oct-2017, as the total unique ID  is 189.

can you share the expression again?

thank you so much

Order Date

(Database column)

Type

(Database column)

count(distinct [ID])

(it is a calculated measure)

29-Sep-2017CCC13
29-Sep-2017EEE4
29-Sep-2017BBB156
30-Sep-2017CCC8
30-Sep-2017EEE15
30-Sep-2017BBB143
21-Oct-2017CCC10
21-Oct-2017EEE24
21-Oct-2017BBB155

Wanted result

 

  

Typecount(distinct [ID])
CCC10
EEE24
BBB155
Total189

Re: Help: maximum from each count

You can try one of these

FirstSortedValue(Aggr(Count(DISTINCT ID), [Order Date], Type), -Aggr([Order Date], [Order Date], Type))

or if each type will always have the same max date, then this

Count(DISTINCT {<[Order Date] = {"$(=Max([Order Date]))"}>} ID)

anson_kong
New Contributor II

Re: Help: maximum from each count

Thank you Sunny,

Community Browser