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

Question

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 · Accepted Answer

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