Oracle Fusion Data Intelligence

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

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

Accepted answer
635
Views
2
Comments
Caroline Blanchard
Caroline Blanchard Rank 6 - Analytics Lead

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?

Best Answer

  • Venkat Koduvayur-Oracle
    Venkat Koduvayur-Oracle Rank 4 - Community Specialist
    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

Answers

  • Venkat Koduvayur-Oracle
    Venkat Koduvayur-Oracle Rank 4 - Community Specialist

    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