Minimum Date
Hi all,
I'm looking to find the first time a customer quoted. I have a transactional table with customer dimension and a quote date dimension. I want to aggregate the dates by customer and return the minimum date for each customer.
I'd like to create this in formulate so I can calculate different metrics such as date since first quote.
I have tried utilizing the Aggregate Block Minimum function with Quote Date as the data point and customer as the list, the Statistical Minimum function with Quote Date as the data point and customer as the list, and the Aggregation Semantic Minimum function with Quote Date as the measure.
Any insights are appreciated, thank you!
4 replies
-
Hi
The easiest way to achieve this is to create a Measure from your Date dimension in your model and use the Minimum aggregation method. In my example here, I've created two Measure from my Date, using the the Minimum and Maximum aggregations and renamed them Earliest and Latest Date. When these Measures are aggregated by other dimensions, like customer, they will show the minimum and maximum date for each customer.
In my case I'm showing the earliest and latest date that a product was sold.
DateTime calculations can be performed on these measures, as in the interval in days between the first and last sale as above, but the Measures are just strings, they are not DateTime datatypes, so they need to be converted using StringToDateTime() before the Date functions can be applied.
Hope that helps!
Ian
-
Thank you ! This makes sense and is a great solution to apply to the data model.
In my case, I don't have access to the data model and would like to create this specifically in formulate for flexibility and future use cases that will come up.
Is this a capability of Pyramid?
-
Hi
You can create these Measures directly from your Date Attribute. Right click on your Date, select Aggregate, then Min or Max. This will generate the min or max date for anything in the rows, in my case the min and max purchase date of the Product concerned. You can then go on to create further calculations as I showed previously.
Hope that helps!
Ian
-
Hi , this is perfect. Thank you!