Find Difference between two dates
Hi All,
We need to find difference between two date dimensions present in our model.
Is it possible to do it using formulate?
7 replies
-
Hi Aryat Srivastava ,
Could you please provide some more details so we could help you?
What data source are you using? (SSAS, pyramid in-memory, Sql Server, Oracle...)
How do you data and model look? Could you maybe add an image with a sample of a grid with your data and what you would want to calculated?
I could say from a high level point of view, using PQL (for non MDX data sources) you can use the DateDiff function:
And you may also also need to use the Caption function to get the date from your members:
https://help.pyramidanalytics.com/Content/Root/developer/reference/fx/PQL/Semantic/String/Caption.htm?Highlight=caption
So maybe something like DateDiff([dim].[date1].caption, [dim].[date2].caption, "dy")
Imbar -
Hi,
depending on what is your final goal, I would use the SQL-Function DateDiff before/during the import of the data into the analytic model. The result (e.g. the difference between the order date and the invoice date in days) would then be a measure.
The most difficult question is, however, what ist the relevant date for the fact table then. Is it the order date or is it the invoice date...? ;-)
Kind regards
Markus -
Hi,
There is a Pyramid function DateDiff() that can be using in formulate to calculate the difference between two dates in various units. See
for further information.
Note that this calculation only makes sense when the Dates are contained within the same table, for example in the FACT /MEASURES Table / Dimension, i.e. there is a 1:1 correspondence between the two dates.
Quite often, the base datekeys in the FACT table are hidden, exposed in the associated Date dimension attributes. Make sure you can see the datekeys in the same table to be able to create the calculation.
Hope this helps,
Ian
-
If you are using a SQL-based model (i.e. IMDB, SQL, Oracle, Redshift), you can accomplish this with the help of a function called StringToDate. Here is an example from a Claims model:
DateDiff(StringToDate([Fact_Claims].[ClaimServiceDateThrough].CurrentMember.Caption, "yyyy-MM-dd"), StringToDate([Fact_Claims].[ClaimPaidDate].CurrentMember.Caption, "yyyy-MM-dd"), "dy")
-
This is great information on identifying a date difference. Is there anyway to see the date difference as a dimension? We are calculating the age of automobile drivers and want to see it as a dimension, since adding up ages as a measure doesn't make much sense.
-
Hi Robert Clark - you can do this in the data model build section when modelling your data in Pyramid.
In the 'Columns' section of the Data Modelling section you are able to make a data item into either a dimension (red) or a measure (blue) - or both, as shown in the first image below.
Once the model has processed, both the measure and dimension version of the data item will be available to use - the 2nd image below shows each customers Age as a measure against the individual Customer ID and the 3rd image shows each all Customer Ages (as a whole year) as a Dimension, with the Customers ID Count Measure (for that Age in Years) value next to it.
I think this should provide you with what you are trying to achieve.
Regards,
Richard.
-
-
Hi Richard,
I know that we can do both measures and dimensions of of a single data model attribute. However, I’m wanting to calculate the age using a date difference between today and their birthdate and show it as a dimension value. Right now I can calculate the age but it can only be represented as a measure.
Thanks,
Rob Clark
