YTD Previous Month by day

Hi,

I have a YTD report that automate on a monthly bases based on YTD that runs on the 9th of each month. In the report the YTD is offset by 7 days therefore the numbers in the report will always be from the beginning of the year 01/01/2018 - 02 of each month driven by Day selector.

I now have a new requirement to produce a new column that displays YTD previous month. Example below.

Report runs on 9th Nov.

Current YTD: Data shows from 01/01/2018 - 02/11/2018 - Value: 200...

Required new column YTD Previous Month: 01/01/2018 - 02/10/2018 - Value: 100...

The next report will be the 9th Jan 2019 and the date rage will be as follow.

YTD: 01/01/2018 - 31/12/2018

YTD PM: 01/01/201/ - 02/12/2018

Can you advise how I can do this dynamically without having to hardcode the cude and change it on a monthly bases.

Many thanks

Find more posts tagged with

Accepted answers

All comments

Previous Member

Hi Minh Doan,

something we came accross recently, was that, when the cube version used in the dataview is on day level, the offset is day-wise. If the used cube version is on month level, the offset is month-wide.

We opened a ticket on this, because it confused us, as some of our cubes had both day- and month-versions. The version picked would also depend, among other factors, on the filter of the screen. So the behaviour became erratic.

So in essence: make sure that your cubes are on day level, then the offset should be day-wise.

And don't mix day- and month-level like we did

BR, Ray

unknown

Hi Minh,

One way of doing it - i like this as it can be modified easily in case requirements change for your custom YTD calc.

You need to:

create an additional "Custom Month for YTD" standard entity with all the months (you can extract months from BOARD time entity and load this entity from the file generated
Create a "Days for YTD -1M Matrix" cube, dimensioned by Day and the newly created "Month for Custom YTD" entity. For every "Custom Month for YTD", this cube needs to be filled with all the days needed in the custom YTD on previous month calc (like below)
Create another "Month to Month Custom YTD Matrix" which simply links the BOARD Month time entity with your custom Month

If you need to have the user able to choose any day (i believe this is not your case) you need, instead of a "Custom Month for YTD" entity, a "Custom Day for YTD" and map every day with the corresponding days needed for the YTD -1 Month

For the report you simply need to have a small procedure that:

Opens up an interactive selection on the Day (or gets the current day for the report from a Select Based On)
selects the month you are running the report for
Select the days needed for the YTD Custom Calc
Injects the days selection into the report block
Refresh screen

(added some extra steps to properly add selection of the day into the screen too)

The first block, with actual data, can simply be an Yearly Cumulated Value, Day Calendar, Offset -7 block.

The second block won't have any time functions on it, but will receive the selection from the opening procedure.

This is what you get, there is a 4 on every data cell in my sample.

So all days from 1st jan to 2nd december: 336 days x4 = 1344

All days from 1st jan to 2nd november: 306 days x4 = 1224

Let me know if you can make it work!

Michele

Previous Member

Thank you Michele,

I will try it out and let you know if it works.