Finding the Date with the most number of orders for every customer

Question & Answer
, , ,
1

How can I find the dimension with the most/least value by a particular calculated measure, partinied by another dimension?

I have this calculated field that returns the date with the most orders by a customer (phone_number):

firstValue( SHIFTDATE, [distinct_count(OrderKey) DESC],[{PHONE_NUMBER}])

When I add it to the table below:

image
image1208×603 34.7 KB

I get this error:

image
image1216×656 15.9 KB

If I add the {SHIFTDATE} field to the table, the table works, but now I have a row for every Shiftdate and phone number, which I don’t want.

2

Hello @Ali_B

Can you try adding “SHIFTDATE” (date field) under values and hide the field?

image
image640×783 54.3 KB

Please let me know if that helped

Thank you,
Shravya

3

If I do that, it works, but now every row is repeated for how many {SHIFTDATE} there are per Phone number. So this does not solve the problem

4

Hi @Ali_B

When using the firstValue function, any fields used in the calculation must be included in the visual to display the value, and you can then hide them if you don’t need them to be visible.

5

Hi @Ali_B

It’s been a while since we last heard from you. If you have any further questions, please let us know how we can assist you.

If we don’t hear back within the next 3 business days, we’ll proceed with close/archive this topic.

Thank you!

6

Hi @Ali_B,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you