Median aid last 12 months

Question

Hello!

I need help with a dashboard in OAC, the condition is as follows:

User selects 13 months, my graph will show cost value, month by month, of these 13 months. In addition, I need the median of this cost to show in the graph, but the median must be calculated based on the last 12 months of the selection made. Does anyone have any tips on how to achieve this by calculating the median this way? Only consider the last 12 months and not the entire selected period?

User_A1BVT · Answer

Dados.xlsx

I couldn't put together the idea to dynamically, according to the user's selection, have the median based on the last 12 periods. I'm attaching an xlsx with a sample of data, would anyone be able to assemble a "dva" file in Oracle Analytics Cloud to help me with this demand?

SiddharthDang-Oracle · Answer

First, create a new column where you rank your measure by period. Now use the rank and create a case statement to test whether the rank number is less than the count of the distinct period selected. You can play your logic of 13 and less than 13 periods here and the output of the case statement will be Y or N. Y -> Take median, N-> Don't take the median. Now calculate the median for measure rows with Y  value.

I am assuming you are using Analysis to build this dashboard. I am not sure about the DV side but you can try there as well.

User_A1BVT · Answer

On the screen, the user has the period filter field. So the user can select 13 months, he can select 15 months, 6 months...

If the user selects 13 months or more the median must be based on the last 12 months. If he selects 12 months or less, the median is based on the selected period.

It's not a single month that's on screen, it's every month (....202107, 202108.... 202201, 202202, 202203, 202204...... 202306)