on - edited on by
DRay
Analytical Need
For a metric, I need to calculate the month difference between the min and max date of purchase for each brand in my dataset. I want the dates to be dynamic in respect to filtering, etc.
I am using the attached .ecdata and .dash files for this solution. See this article on importing a .dash file, and this on importing an .ecdata file.
Challenge
The MIN() and MX() dashboard functions do not work on date fields. And even if we create a Numeric Representation Of Date Fields, a simple subtraction of the results won't work because we can't just use the difference in days (think of the case where the dates are two days apart, but land in different months).
How can we identify the earliest and latest date and calculate the month difference between them?
ElastiCube Prep(Step 1)
Data Requirements
- In the ElastiCube, create a custom field called [Date Numeric] to convert your date field to an integer using
10000*getyear(Date)+100*getmonth(Date)+getday(Date)
- Create this field in the fact table since it is a value we will be performing a calculation on.
Dashboarding Solution (Step 2)
We need to extract the year and month values from our [Date Numeric] field in order to calculate the month difference between to date values. To do this, we will reverse the logic used to convert the field to an integer.
The formula syntax below achieves this for all three components of the earliest date.
To generate the year, month and day corresponding to the latest date value, we can swap out MIN for MAX. Following examples in SQL code.
Year of MIN Date Integer:
FLOOR(min([Date Numeric])/10000)
Return the Month of MIN Date:
FLOOR((min([Date Numeric])-(10000*(FLOOR(min([Date Numeric])/10000))))/100)
Return the Day of MIN Date:
(min([Date Numeric])-(10000*(FLOOR(min([Date Numeric])/10000))))-(100*(FLOOR((min([Date Numeric])-(10000*FLOOR(min([Date Numeric])/10000)))/100)))
For this specific use case, we want to get the month difference between the min and max:
((FLOOR(max([Date Numeric])/10000) - FLOOR(min([Date Numeric])/10000) )*12)
+
(floor((max([Date Numeric])-(10000*(FLOOR(max([Date Numeric])/10000))))/100) - floor((min([Date Numeric])-(10000*(FLOOR(min([Date Numeric])/10000))))/100))
((FLOOR(max([Date Numeric])/10000) - FLOOR(min([Date Numeric])/10000) )*12)
+
(floor((max([Date Numeric])-(10000*(FLOOR(max([Date Numeric])/10000))))/100) - floor((min([Date Numeric])-(10000*(FLOOR(min([Date Numeric])/10000))))/100))
