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
Anyway to display list of dates between a date range
Say for example i have something like this returned in an analysis,
| Procedure Date | Report Date | Procedure number |
|---|---|---|
| 1/1/2019 | 5/1/2019 | 123456 |
but i would like to get something like this
| Calender Date | Procedure Date | Report Date | Procedure number |
|---|---|---|---|
| 1/1/2019 | 1/1/2019 | 123456 | |
| 2/1/2019 | 1/1/2019 | 123456 | |
| 3/1/2019 | 1/1/2019 | 123456 | |
| 4/1/2019 | 1/1/2019 | 123456 | |
| 5/1/2019 | 1/1/2019 | 5/1/2019 | 123456 |
so essentially for any historic Calendar date i can determine how many null (unreported) procedures
thanks
Answers
-
How about complex join with Date Dimension
Date_Dim.Date >= Procedure_Date and Date_Dim.Date <= Reported_Date
0 -
The above logic will repeat the rows.
Calender Date
Procedure Date
Report Date
Procedure number
1/1/2019 1/1/2019 5/1/2019 123456 2/1/2019 1/1/2019 5/1/2019 123456 3/1/2019 1/1/2019 5/1/2019 123456 4/1/2019 1/1/2019 5/1/2019 123456 5/1/2019 1/1/2019 5/1/2019 123456 If you are looking to populate Report Date to the last record then have a calculated column CASE WHEN Date_Dim.Date = Reported_Date THEN Reported_Date ELSE NULL END
Calender Date Procedure Date Report Date Procedure number CalCulated_Column 1/1/2019 1/1/2019 5/1/2019 123456 2/1/2019 1/1/2019 5/1/2019 123456 3/1/2019 1/1/2019 5/1/2019 123456 4/1/2019 1/1/2019 5/1/2019 123456 5/1/2019 1/1/2019 5/1/2019 123456 5/1/2019 0 -
thanks but i dont have access to any back end functionality to create these additional dimensions - only have access to the front end reporting tool
0 -
I doubt you'll be able to do it if you don't have the ability to change the way the semantic layer is created.
We'd also probably need to know whether the fields you've listed come from a single table or multiple.
For example, if one table had a date for every day and your second table just had a few dates, then all you need to do is make sure that there's a left join applied from table 1 to table 2.If you can't get the RPD changed, then you may need to request access to source your analysis using direct SQL but often companies don't like letting their users do that (I know we don't) due to the ability for the users to write bad code and then execute it multiple times in quick succession (know from example).
0 -
982332 wrote:
If you are looking to populate Report Date to the last record then have a calculated column CASE WHEN Date_Dim.Date = Reported_Date THEN Reported_Date ELSE NULL ENDCalender DateProcedure DateReport DateProcedure numberCalCulated_Column1/1/20191/1/20195/1/2019123456 2/1/20191/1/20195/1/2019123456 3/1/20191/1/20195/1/2019123456 4/1/20191/1/20195/1/2019123456 5/1/20191/1/20195/1/20191234565/1/2019
How would this work if he's only got one row returning?
It looks like there's most likely an inner join in the subject area that's causing only the rows with a date match to return. Hence why he has 1 row and not the 5 he wants.0 -
Do you actually have a date dimension not linked to "procedure date" and "report date"?
If you don't and with just access to the front-end you can't do much: OBIEE will never generate rows which don't exist in the result of the query, the query is driven by the model (to which you don't have access as you said).
That's why if you don't have a date dimension not linked to those 2 dates but linked just to something else you can leave it here as you just can't.
0 -
If you have access to a data model that has a date record for every day, you can union-in a daily record to fill your missing dates.
In this example, I want to show how many contracts were awarded on each day between March and November.
I know there were many days with no awards, so I union-in filler dates from a financial system that I know has transaction records for every day.
Place the date column into a pivot table to combine records.
This method becomes impractical if you have a very complicated query, but it's simple if you only have a few columns.
0 -
If you are looking from front end only how about advanced SQL
select b.s_1, a.s_1,a.s_2,a.s_3
from
(SELECT
"Your Subject Area"."Procedure Date" s_1,
"Your Subject Area"."Report Date" s_2,
"Your Subject Area"."Procedure Number" s_3,
FROM "Your Subject Area"
) A,
(select "Date Dimension"."Date" s_1 FROM "Your Subject Area") B
WHERE B.s_1 >= A.s_1 AND B.s_1 <= A.s_2
0
