8 Replies Latest reply: Dec 19, 2017 3:16 AM by kong ch

# 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

• ###### Re: Help: maximum from each count

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

• ###### Re: Help: maximum from each count

max(total <Type> YourCount)

• ###### 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]))

• ###### Re: Help: maximum from each count

Thank you all,

I forget post the expression of my count.

• ###### 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)

• ###### Re: Help: maximum from each count

Thank you Sunny,