Knowledge Base Article

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

 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 filter...
Updated 02-01-2024
Analytics: Dashboard Design
Laflet's avatar
Laflet
Cloud Apps
08-04-2023

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?

 

About Data Models

Node avatar for Data Models
Guides for doing Data Modeling
49 ArticlesCreated: 6 months agoLatest Activity: 06-17-2025