How to solve YTD , MTD, QTD problems

[inu]

Hi
I m a beginner of YTD, MTD, QTD. As i want to know what problems should be , when working with same. Can you refer me about it to know from begging to complexity. Where can i get relevant problems to practice. such as actual year, casting problem, previous year problems. and all.
Please help me out.

Regards
Inam

[MFGF]

Hi,

YTD is Year To Date. It means the period from the beginning of a year up to and including the current date. So YTD this year would be a period spanning from 1st January 2014 to 6th March 2014. YTD last year would be 1st January 2013 to 6th March 2013. And so on.

MTD is Month To Date. it is exactly the same concept as YTD above, but within the context of a month rather than a year. MTD this month would be 1st March 2014 to 6th March 2014. MTD last month would be 1st February 2014 to 6th February 2014 etc.

It's exactly the same for QTD - Quarter To Date, but for quarters.

These kinds of requirements are easy to implement if you have a dimensional package. There are some powerful dimensional functions (such as periodsToDate()) which allow you to take the current period and return all members from a set point up to and including the current one.

If you have a relational package it can be more of a challenge. Often you will use a filter - for example
[order date] between _first_of_month(current_date) and current_date
would return the MTD orders

Sometimes you need to extract the year from a date and use that, for example:
extract(year, [order date]) = extract(year, current_date) AND [order date] <= current_date
would return the YTD orders

The exact solution will depend upon how you store your dates - are they text strings, numbers or date items? Each would require a different approach.

Just some random thoughts.

MF.

[inu]

HI MFGF
Thanks a lot!!!  u have cleared some daughts were there in my mind.

Now i want to know when i need to use the cast function. i know how to cast, i m asking about the scenario, Because when i wrote in expression editor
CASE
WHEN [TIME_ID] BETWEEN 2000-01-01 AND current_date THEN [Presentation Layer].[Sales].[AMOUNT_SOLD]
END
.
It was working properly. then where i need to cast from string to date...
Please get my point , what i want to ask... please give one scenario regarding this, so that i can clear my daughts.


Thanks
Inam

[MFGF]

HI MFGF
Thanks a lot!!!  u have cleared some daughts were there in my mind.

Now i want to know when i need to use the cast function. i know how to cast, i m asking about the scenario, Because when i wrote in expression editor
CASE
WHEN [TIME_ID] BETWEEN 2000-01-01 AND current_date THEN [Presentation Layer].[Sales].[AMOUNT_SOLD]
END
.
It was working properly. then where i need to cast from string to date...
Please get my point , what i want to ask... please give one scenario regarding this, so that i can clear my daughts.


Thanks
Inam

Hi,

Casting is simply a means of converting a value from one data type to another - for example, changing a string to a number or a number to a string or a date to a string, etc. Let's say you have a YEAR item in your data which is stored as a character value in the form YYYY eg "2014"

If you want to be able to use this in a filter and compare it with the year extracted from the current date, you have a problem. When you extract parts of dates the returned values are numbers - so if you have an expression extract(year, current_date) it would return a value of 2014 but as an integer data type. You cannot mix data types when comparing values in a filter, so one option would be to cast this to a string (char) of four characters so you can compare it with your string YEAR item. Your filter expression could then be:

[YEAR] = cast( extract( year, current_date ), char(4) )

Is that what you wanted?

Cheers!

MF.