Comparing dates in a formula
What would be the best practice for comparing two date dimension fields in a formula? Here's an example formula we are trying to create:
if(created_date<=evaluation_date, 1, 0)
created_date and evaluation_date are date dimension attributes
2 replies
-
Hi
You must remember that the Pyramid models are dimensional in nature. Thus, each dimension (hierarchy/attribute) is independent of the others, the members of which are made up of the unique, ordered values from that column in the underlying table. Their only semantic relationship to each other is through any joined tables, typically a FACT table of some sort. Therefore an expression like the one you quote has no real meaning. Which created_ date with which evaluation_date are you comparing?
Typically, these types of date evaluations are comparing two dates in the same record in the underlying table, which gives the dates you are comparing context. In this case you need use the "Custom Column" feature and create a new virtual column in the underlying table that is calculated according to your formula.
Here's a model where in the underling table I have an Order Date and a Shipping Date. They belong to the same record in my table, but are represented as two independent dimensions in Pyramid's Semantic Model.
To compare them I will need to create a new "Custom Column", creating a new virtual column in the underlying table. Use formulate and choose Custom Column:
Build out your formula as required. Here, I'm replicating yours:
It's not a great example as my Shipping Date will always be later than the Order Date (unless something has gone weirdly wrong!).
So here's an example that calculates the average Lead Time between the Order Date and the Ship Date:
I'm using the DateDiffence() function, passing it the Order and Ship Date columns and it calculates the difference in Days (you can use other time periods). In the right hand panel I've set this column to be a Measure using Average as the aggregation rule and I'm hiding the attribute itself.
I can then use this Measure in my Discover:
Hope that helps.
Ian