Oracle Analytics Forum

Home› Oracle Analytics and AI› Oracle Analytics and AI Forums› Oracle Analytics Forums› Oracle Analytics Forum

Categories

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

How to utilise an unbroken time series in a visualisation?

Accepted answer
25
Views
6
Comments
Dave Brandon
Dave Brandon Rank 2 - Community Beginner
in Oracle Analytics Forum

We have a requirement to produce a Pivot Visualisation that includes an unbroken series of dates, from a filterable period, and the value of whatever measure is relevant.

For example, in a period of 16/03/26 - 22/03/26 being able to see the number of activities a Resource has handled, including dates when the value is 0/NULL. Using an activity table we can get the below. which is expected as this Resource handled no activities on the missing dates:

image.png

We're struggling to get the 2 missing dates included as 0/NULL as well. We have previously had this working using an Excel file of dates up to 2030 linked in the Workbook to the dataset on the relevant date column, ROUTE_DATE, in this case. But, while that legacy report still functions, we're unable to replicate it on newer tables.

In an effort to replace the Excel file, in a Dataset we have identified a "Time" table from our ADW instance and linked it with a Full Outer Join to the Activity table, but the result is the same as above.

image.png

We feel like we're missing something obvious here, as even Googles AI response says this is all that is needed, but we continue to fail.

Can anyone advise how to achieve an unbroken series of dates for a measure, even if there is no data stored for some of the dates?

Tagged:

Best Answer

  • Gianni Ceresa
    Gianni Ceresa Mod
    Answer ✓

    I just gave it a quick try this morning while I was checking OAS 2026.

    With DV datasets, even when setting a join as an outer join, it kept doing an inner join. But because it is between 2 files, I wasn't expecting much as the product has always been bad at doing the job of a database,

    If you have a semantic model, that's a easy change as joins are respected. With DV datasets, I don't expect much by default. Am I shocked it doesn't respect the outer join definition between the 2 files in the dataset? Not really.

    I consider it a bug, because if I can explicitly set an outer join and it performs an inner join, that's what it is. But they will probably say it's a feature and "works as designed".

    If you can, model things in the semantic model and you will be fine. Or generate a consolidated dataset with the NULLs in the rows that will need to be materialized.

    You can also post an idea asking for this "feature".

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi,

    Just to be sure: you are working only with datasets, there is no semantic model involved in this, right?

  • Dave Brandon
    Dave Brandon Rank 2 - Community Beginner

    That's correct, at this point there's only datasets.

  • Dave Brandon
    Dave Brandon Rank 2 - Community Beginner

    @Gianni Ceresa , do you know if this is something that can be achieved? We feel like we have all the pieces of the puzzle, but just cannot get them to fit together correctly.

  • Gianni Ceresa
    Gianni Ceresa Mod

    There could be a hidden checkbox somewhere in the visualization (I had a quick look and didn't see one), therefore somebody else could maybe have an easy solution (I admit of having a bias against DV and its components).

  • Daniel Ryan
    Daniel Ryan Rank 5 - Community Champion

    @Dave Brandon have you tried adding an ifnull formula? Statistically, breaking the time series is correct as technically null is different to zero so if you created a different column and idi ifnull(column,0) then this may work. You could then hide the original column and only expose the new one.