How to create a measure to calculate max(date) per ID filtered by a value of dimension

Hey guys,

I am trying to create a measure for date field. Below is the current view in my explore:

09b54402-c44d-4d42-a82a-1897fcc1e999.png
Current View

This view captures the change in the status of a customer and the date associated with it. As of now this view has repeated IDs, I am trying to create a single row for each ID, which will show all the statuses as different columns (stage changed to), and the max(stage change date) associated with each of them. It will look like this:
 

97a677ce-c768-4bc3-ae09-ed05741d3500.png
Expected view

I tried using this measure:

3b68291b-963d-428c-b2a2-00a750842ef4.png

But unfortunately, the explore is not able to show the date (maybe because the type is max, rather than date). Now if I am using type: Date, then I won’t be able to use the filter parameter.

Can someone please help me in finding the workaround for this?

Solved Solved
0 2 5,305
1 ACCEPTED SOLUTION

How about this:

measure: MQL {
    type: string
    sql: max(${stage_change_date_date}) ;;
  }

And in your report, add ID and MQL and add Stage Changed to as PIVOT column

View solution in original post

2 REPLIES 2

How about this:

measure: MQL {
    type: string
    sql: max(${stage_change_date_date}) ;;
  }

And in your report, add ID and MQL and add Stage Changed to as PIVOT column

How about this:

measure: MQL {
    type: string
    sql: max(${stage_change_date_date}) ;;
  }

And in your report, add ID and MQL and add Stage Changed to as PIVOT column

Wow! You are a genius! Thank you so much, Jennifer! You are a lifesaver! It worked like a charm. The solution was so simple, and yet so elegant. God bless you!

Top Labels in this Space
Top Solution Authors