nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

How to get the value of the last month in the quarter

BenFreez

Hi,

I''m trying to get the value against a quarter. I have 2 tables Time dimension and Fact table.

I want to display the value against each quarter with the specificity below :

Consider quarter Q1 it should display the value of 3rd month, Q2 should display value for 6th month etc...

And if in any particular Quarter we dont have a value for last month then it should display the value for 2nd month, if 2nd month value is also not present then it should display 1st month sales value.

Please can you guide me on the best way to realise this ?

Best regards,

Ben

Find more posts tagged with

Accepted answers

All comments

Robert Angel

Best way to do this is to build it into your time hierarchy, especially if it is a corporate need that will be heavily used throughout your subject areas.

To do this you would have a hierarchy as below

Total ¬ Year ¬ Quarter ¬ Month ¬ Date (if applicable)

Then merely by having your fact measure(s) defaulting to sum by including the quarter in a report with the measure in question you will get the functionality you describe.

If it is a one of your could use some case logic to convert your months into quarters.

case when Month in ('Jan','Feb','Mar') then 'Q1'

---etc

BenFreez

Hi Robert,

I tried to use the time hierachy as you mentionned but the problem is that the value of the quarter showed is the sum of Jan + Feb + Mar for Q1 and in my case i only need Mar for Q1.

For example

Q1 :

- Jan : 100

- Feb : 200

- Mar : 450

==> I want to see only Mar (450) for Q1

Q2 :

- Apr : 15

- May :45

==> I want to see only May(45) for Q2

And i'm trying to do it dynamicaly for each year

Best regards,

Ben

Robert Angel

Sorry, missed your 'last' month requirement, for this you really need the quarter to be in your hierarchy then QTD (To Date measure built against the Quarter) measures would be ideal.

You can probably also do it with case logic, but given your additional 'last month that has data in the quarter' then what I describe is definitely better.

BenFreez

Hi Robert,

Thanks a lot for this solution, i will test it and come back to you to confirm if it's solving my problem

Robert Angel

Note also that in the aggregation options on your fact measures you have first / last. This would not work though if your (say) March was null in Qrtr1 and you wanted February that isn't...

Walter de Wit

Perhaps you can work something out with RMAX() by year, quarter. Maybe this will solve your puzzle if the toDate() function is not doing what you expect from it.

BenFreez

Hi Walter !

Exact the RSUM() function is also very interesting for my need.

THanks a lot for your help

Ben