Oracle Fusion Data Intelligence

Welcome to the Oracle Analytics Online Forum!

We're thrilled to have you join our community of analytics enthusiasts and professionals. To enhance your experience and foster meaningful interactions, we encourage you to personalize your profile by setting up a display name and uploading a profile picture. Your display name will be how others recognize and engage with you in discussions, while a profile picture adds a personal touch to your forum presence.

Take a moment to update your profile with a display name and an image representing you. Let's create a vibrant and engaging community together!

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

Accepted answer
575
Views
2
Comments
Caroline Blanchard
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?

Best Answer

  • Venkat Koduvayur-Oracle
    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

  • 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