YTD?

[dssd]

I have dimensional report where i need to display YTD information for a measure. How best can i display it? I need to use relative time category?

[cognostechie]

It depends on the format of the report. You can either set a filter to the query to determine YTD or use a calculated column with that filter.

[dssd]

But, usage of relative category is correct right? Is it a good practice to calculate YTDs in a relational report, calculation would be ugly. I thought of using cubes and reducing the processing time since all the info was already there. Does it make sense?

[RobsWalker68]

Hi,

If you are using dimensional data and particularly a transformer derived cube then you can use relative time categories if you wish to base YTD on the current date.

Alternatively if you wish the user to select a period and then base the YTD calculation on that you can use the periodsToDate function as with the example below below

aggregate(currentMeasure within set periodsToDate ([my cube].[All Dates].[All Dates].[Year],[Selected Month]))

[Selected Month] in this example is a prompted item using the syntax: [Month level] -> ?pr_selectedmonth?

Kind Regards


Rob



   

[cognostechie]

Like Rob said, if you already have YTD in the cube (which Transformer creates by itself), you can just use that. That being said, it is not a bad practise to create YTD in the report itself if you have to use a relational package. The calculation is quite neat and clean and in no way ugly and works quite speedily.

extract(year,[Date]) = extract(year,current_date) and [Date] <= current_date.

This is also a relative time category even if it is in Report. I usually create all this in FM but can also be done in RS as both use the same functions. This is for the calendar YTD. For fiscal, I am just wrapping up the relative time categories in FM Model and it's working like a charm. Will post it if anybody needs it but it might have to be tweaked as fiscal may be different for different environments.