How to do YTD year on year % calculation in report studio by using DMR package

[prabhakar.p]

Hi All,

I have a report where I have to calculate the YTD year on year growth rate % for Sales.
Basically, I need to calculate both the YTD and PYTD to get the Year on Year growth %.
please provide any suggestion to achieve this

Thanks,
Prabhakar.

[MFGF]

Hi All,

I have a report where I have to calculate the YTD year on year growth rate % for Sales.
Basically, I need to calculate both the YTD and PYTD to get the Year on Year growth %.
please provide any suggestion to achieve this

Thanks,
Prabhakar.

Hi,

Some initial questions, as you haven't really given us very much detail to work with:

1. What is the exact definition of YTD in your case? Is it to date (ie from the beginning of the year to a current day member) or is it up to the current month, or something else?
2. What levels do you have in your date hierarchy?
3. How are you identifying the current date (or month or whatever else)? Is it the last day member in your DMR level, or are you prompting for it, or does it need to be calculated from the current system date, or something else?


Let's make some guesses for now. I'm going to assume you are looking at YTD up to the current day member in your DMR day level. I'm going to assume you have Year, Quarter, Month and Day levels in your hierarchy. I'm also going to assume that the current day member is the last one we find on the Day level.

So, first how do we identify the current day member? The closingPeriod() function will return the last member on a level:

closingPeriod([your Day level])

Now we have this, we can figure out all the day members from the beginning of the year to the last member. The periodsToDate() function will give us these:

periodsToDate([your Year level], closingPeriod(your Day level]))

Now we have these day members, we can aggregate our measure across them to give us our measure total for this period (ie this is our YTD measure value):

aggregate(currentMeasure within set periodsToDate([your Year level], closingPeriod([your Day level])))

Now we have YTD, it's a similar story to get PYTD. We can use the parallelPeriod() function to take the current day member and get the same day in the prior year:

parallelPeriod([your Year level], 1, closingPeriod([your Day level]))

Then we can use the same approach as above to grab the day members from last year up to and including this day, and aggregate your measure across them to give us a PYTD measure value:

aggregate(currentMeasure within set periodsToDate([your Year level], parallelPeriod([your Year level], 1, closingPeriod([your Day level]))))

Now you have the expressions for YTD and PYTD you can calculate your Year on Year growth %

Cheers!

MF.

[prabhakar.p]

Hi MF,

Thanks for your valuable inputs.

The YTD calculation should for system date from the beginning of the Financial year. for eg: FY 2017 (from Jul -16 to till date)
In my Time hierarchy I have Year ->Quarter->Month->Day
I am not prompting any date in report prompt. I just have year and quarter prompt for my report.


Does these closingperiod function will work for system date ?

Regards,
Prabhakar.

[prabhakar.p]

Hi,

I have used "aggregate(currentMeasure within set periodsToDate([your Year level], closingPeriod([your Day level])))" to get the YTD

but I see the same YTD measure values for the other years as well. eg: When I run the report, I selected for 2016 and 2017. in my report output I see the same YTD values for both the years which is incorrect.

would you please help to get the correct results.

Regards,
Prabhakar.

[MFGF]

Hi,

I have used "aggregate(currentMeasure within set periodsToDate([your Year level], closingPeriod([your Day level])))" to get the YTD

but I see the same YTD measure values for the other years as well. eg: When I run the report, I selected for 2016 and 2017. in my report output I see the same YTD values for both the years which is incorrect.

would you please help to get the correct results.

Regards,
Prabhakar.

Hi,

Well, that's a rather different requirement than you told us originally. You simply said you wanted YTD and PYTD so you could perform a calculation to show the % difference. There was no mention of prompting for years. What could (and does) happen in this situation is that your fellow forum members spend time and effort answering the wrong question because you didn't make enough effort to describe what you really wanted. Can you put yourself in that position for a moment? How would it make you feel if you spent over an hour giving someone a detailed answer, describing every step of the process to aid their understanding, only for them to reply "that's not really what I wanted after all"?

There are lots more questions we need answers to before we can point you in the right direction:

1. You are prompting for years and quarters in your report. Can you tell us what mechanism you are using for this? Detail filter? Dimensional expression in a query calculation? Slicer filter? Something else? Can you share the expressions you are using?
2. What happens if a user selects more than two years in your year prompt? How would you do a % difference across more than two years?
3. What is the reason for prompting for quarters if you are doing YTD vs PYTD calculations in your report? Can you explain how your report should work?
4. You mentioned you want YTD to be based on the system date, but you also told us that the closingPeriod() approach gives you what you need for the current year. Why do you need this approach to be changed to use the system date instead? What happens if you don't have a member for the system date in your data when the report is run? Can you be specific about which approach is the correct one?

You need to give us as much detail as you can about how the report works before we can advise you how to proceed. Having already spent a lot of time formulating an answer based on incomplete information that subsequently appears not to be what you need, it doesn't make sense to continue that approach. Tell us all the details, then once we know what you really need to do we can advise.

Cheers!

MF.

[prabhakar.p]

Thank you very much to getting back and helping me on this.

I understood that, you are spending the your valuable time to answers questions. I am trying to give you more information.

Please let me know if you need any other information.

1. You are prompting for years and quarters in your report. Can you tell us what mechanism you are using for this? Detail filter? Dimensional expression in a query calculation?
Slicer filter? Something else? Can you share the expressions you are using?

Its detailed filter on Year and Quarter

Year Fitler - [DMR Model].[Time].[Time].[Year] in (?Year?)
Quarter Filter - [DMR Model].[Time].[Time].[Quarter] in (?Quarter?)

Quarter prompt is optional.

2. What happens if a user selects more than two years in your year prompt? How would you do a % difference across more than two years?

We recommend user to select only 2 years.

3. What is the reason for prompting for quarters if you are doing YTD vs PYTD calculations in your report? Can you explain how your report should work?

its cross tab based report and users should able to see the sales/quota information for selected years and quarters.

the report design is like below.

on cross tab rows I have managers info
on cross tab columns I have quarter and nested the following fields sales_amt,quota_amt and other calcualted fields based on these fields along with this I have display the YTD YoY% growth and that is the reason I have requested YTD and PYTD calculation.


4. You mentioned you want YTD to be based on the system date, but you also told us that the closingPeriod() approach gives you what you need for the current year.
Why do you need this approach to be changed to use the system date instead? What happens if you don't have a member for the system date in your data when the report is run?
Can you be specific about which approach is the correct one?

Basically, I have future dates in Time dimension and thats the reason i wanted to use the system date but later I have updated the time dimension at day level key to use the last transaction date from fact table.
this given me expected resutls. But, the problem is when I select 2 years from the prompt without selecting any quarters for the current year and previous year it shows the same value (current year YTD value).


Thanks again for your time.

Regards,
Prabhakar

[MFGF]

1. Its detailed filter on Year and Quarter

Year Fitler - [DMR Model].[Time].[Time].[Year] in (?Year?)
Quarter Filter - [DMR Model].[Time].[Time].[Quarter] in (?Quarter?)

Quarter prompt is optional.

Ok. That's a big problem. Detail filters are a concept you use with relational packages, but here you're using a dimensional package. Using detail filters with dimensional packages can give you incorrect and inconsistent results. Take a look at FAQ #4 in this post: http://www.cognoise.com/index.php/topic,27563.0.html

2. We recommend user to select only 2 years.

It's not often I'm left speechless, but this is one of those moments. You recommend? Really? Is that a robust, trusted, foolproof approach to making sure your report returns sensible, appropriate results? Really? Why do you need to give the users the ability to select more than two years? What is the business requirement? Is it to see the current year and prior year? The current year and an earlier year than the prior year? Something else? Can you explain?

3. its cross tab based report and users should able to see the sales/quota information for selected years and quarters.

the report design is like below.

on cross tab rows I have managers info
on cross tab columns I have quarter and nested the following fields sales_amt,quota_amt and other calcualted fields based on these fields along with this I have display the YTD YoY% growth and that is the reason I have requested YTD and PYTD calculation.

You mentioned your Quarter prompt is optional. Which quarter(s) should be displayed if no prompt values are chosen? Is the YTD YoY% nested below each quarter or is it stacked alongside the quarters? Can you explain the interaction between quarters and this calculation?

4.  Basically, I have future dates in Time dimension and thats the reason i wanted to use the system date but later I have updated the time dimension at day level key to use the last transaction date from fact table.
this given me expected resutls. But, the problem is when I select 2 years from the prompt without selecting any quarters for the current year and previous year it shows the same value (current year YTD value).

To be able to advise here, you need to answer the above questions.

I suspect you might only need to prompt for a single year, and compare this with the current year - is that the case? If not, can you explain why you need to prompt for two years and what should happen if the current year isn't one of them?

MF.

[prabhakar.p]

Hi MFGF,

Here is more detailed information.

My business requirement is, by default the report has to run for the current quarter with the measure columns (eg: sales amount) along with the FYTD Growth Rate (on sales) and QoQ growth Rate (o sales) columns.
Business also expecting the Prompt to select the other quarters. User also expecting the for the selected quarter is possible to get the QoQ and YTD growth rate. is it possible ? as I know YTD is only for current year start date to today' date. (please suggest).

Please advice what is way to achieve this.

Thanks,
Prabhakar