cancel
Showing results for 
Search instead for 
Did you mean: 
intapiuser
Community Team Member
Community Team Member
 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 .sdata 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.2019-01-29_10h56_18.png
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. 
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:
Final Solution  - Month Difference Formula:
((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))
Rate this article:
Comments
Laflet
9 - Travel Pro
9 - Travel Pro

this should get you Day diff.

It assumes a 30-day month and doesn’t account for edge cases, such as leap year, etc.

((FLOOR(max([Date Numeric])/10000) - FLOOR(min([Date Numeric])/10000)) * 365) +

((FLOOR((max([Date Numeric]) - 10000 * FLOOR(max([Date Numeric])/10000))/100) - FLOOR((min([Date Numeric]) - 10000 * FLOOR(min([Date Numeric])/10000))/100)) * 30) +

((max([Date Numeric]) - 10000 * FLOOR(max([Date Numeric])/10000) - 100 * FLOOR((max([Date Numeric]) - 10000 * FLOOR(max([Date Numeric])/10000))/100)) -

(min([Date Numeric]) - 10000 * FLOOR(min([Date Numeric])/10000) - 100 * FLOOR((min([Date Numeric]) - 10000 * FLOOR(min([Date Numeric])/10000))/100)))

Any idea how to deal with those edge cases?

 

Version history
Last update:
‎02-01-2024 10:44 AM
Updated by:
Contributors