How to get calculation involving dates to treat as a measure rather than an attribute?

Caroline Blanchard

Summary: The result of a calculation being an attribute prohibits the use of calculation in visualizations. I would like to convert it from a time attribute to a measure attribute

Content: I would like to visualize how long a requisition is pending approval. To do so, I created a calculation which takes the current date minus the requisition creation date when the status is pending approval. The new column shows as an attribute which cannot be used in scatterplots, tiles, or bar charts.

Code Snippet:

I have tried casting it as a DOUBLE and an INT, but have not had luck with either. Additionally, I have tried casting current date and Requisition creation date individually, but did not have luck there either.

Is there a way to cast time attributes to INT's or DOUBLE? If not is there a way to use the time attributes on visualizations and have it work?

Venkat Koduvayur-Oracle

Hi @CarolineAnderson

Furthermore, we can add a formula Fact.Colum1-Fact.Colum1 to the ELSE condition which will always equates to 0.

So we don't need to worry about ELSE being evaluated if the first condition is false.

Its just an hack to make the calculation as a measure column

CASE WHEN Requisition Status='Pending'

THEN CURRENT_DATE-Requisition Create Date

ELSE Requisition Amount - Requisition Amount

END

Regards

Venkat

Venkat Koduvayur-Oracle

Hi @CarolineAnderson

You could add an ELSE condition to your CASE statement and then add a fact measure column to the ELSE part of your condition. As far as your query is not evaluated for ELSE and satisfies your first criteria, this calculation measure would give you the desired results and this way it can be treated as a measure column and not attribute column.

Sample below

Hope this helps.

Regards

Venkat