Calculate the Month Difference Between Min and Max Date Fields from the Dashboard (without scripting)

[intapiuser]

 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.

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))

[Laflet]

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?