Categories
- All Categories
- 75 Oracle Analytics News
- 7 Oracle Analytics Videos
- 14K Oracle Analytics Forums
- 5.2K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 40 Oracle Analytics Trainings
- 59 Oracle Analytics Data Visualizations
- 2 Oracle Analytics Data Visualizations Challenge
- 3 Oracle Analytics Career
- 4 Oracle Analytics Industry
- Find Partners
- For Partners
How to get calculation involving dates to treat as a measure rather than an attribute?
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
-
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
0
Answers
-
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
0