Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
I would like to set the formula to get the maximum from each count as below.
Result
Type | EachCount |
---|---|
ABC | 13 |
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) |
---|---|---|
ABC | 29-Sep-2017 | 13 |
ABC | 19-Oct-2017 | 12 |
ABC | 03-Oct-2017 | 11 |
ABC | 21-Oct-2017 | 10 |
ABC | 10-Oct-2017 | 9 |
ABC | 12-Oct-2017 | 9 |
ABC | 17-Oct-2017 | 9 |
ABC | 20-Oct-2017 | 9 |
ABC | 23-Oct-2017 | 9 |
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]))
how the count is derived? how you are getting 13 and all that numbers?
max(total <Type> YourCount)
you can try
=firstsortedvalue(type,-Eachcount)
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]))
Thank you all,
I forget post the expression of my 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-2017 | CCC | 13 |
29-Sep-2017 | EEE | 4 |
29-Sep-2017 | BBB | 156 |
30-Sep-2017 | CCC | 8 |
30-Sep-2017 | EEE | 15 |
30-Sep-2017 | BBB | 143 |
21-Oct-2017 | CCC | 10 |
21-Oct-2017 | EEE | 24 |
21-Oct-2017 | BBB | 155 |
Wanted result
Type | count(distinct [ID]) |
---|---|
CCC | 10 |
EEE | 24 |
BBB | 155 |
Total | 189 |
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)
Thank you Sunny,