6 Replies Latest reply: May 5, 2017 3:08 AM by Ravi Kishore

# Prior Year YTD, Prior Year MTD,Prior Year QTD in Absence of Date Field

Hi Friends,

I have attached the Data Sample . I dont have date Field in my Data and i cant use Date field as my data has Granularity for Month wise only. so i cant use today function .

I was able to calculate MTD,QTD, YTD for Current Year.

 v\$MTD Sum({}TY_NETDOLLARS)/1000000 v\$QTD Sum({}TY_NETDOLLARS)/1000000 v\$YTD Sum({}TY_NETDOLLARS)/1000000

But stuck in Creating MTD, QTD YTD for Previous Year

It would be great help if some one can through some pointers.

• ###### Re: Prior Year YTD, Prior Year MTD,Prior Year QTD in Absence of Date Field

You can use the first (or last) day of the month as a base date.  That is, for May 2017, it would be 05/01/2017 - and build your YTD, QTD, MTD from that date.

• ###### Re: Prior Year YTD, Prior Year MTD,Prior Year QTD in Absence of Date Field

How should i map my Month from the data file with the Date you are mentioning.

Because i dont have a Date Field in my data, also as you said how to derive first or last month in absence of date field..

Thanks,

Ravi

• ###### Re: Prior Year YTD, Prior Year MTD,Prior Year QTD in Absence of Date Field

Your month format is FYYYYY-MM, so to get the start of the month will be

makedate(mid(TYMONTH,3,4),right(TYMONTH,2))

or even shorter

date(date#(TYMONTH,'FYYYYY-MM'))

Both will return a full date, the first day of the month..

• ###### Re: Prior Year YTD, Prior Year MTD,Prior Year QTD in Absence of Date Field

Thanks  Michael.

This is what i have done

date(date#(TYMONTH,'FYYYYY-MM')) as FDM,

In Resident Table i have written below

Stuck for QTD and MTD.

Regards,

Ravi

• ###### Re: Prior Year YTD, Prior Year MTD,Prior Year QTD in Absence of Date Field

First, I think you can simplify the YTD conditions:

FDM >= YearStart(today(),-1) and FDM <= addmonths(MonthEnd(today()),-13)

I'd use it as a flag, it will help to build simpler front end expressions:

if(FDM >= YearStart(today(),-1) and FDM <= addmonths(MonthEnd(today()),-13), 1, 0) as PriorYTD

For QTD, similar:

if(FDM >= QuarterStart(today(),-1) and FDM <= addmonths(MonthEnd(today()),-4), 1, 0) as PriorQTD

Now, the bigger question is not technical, but business logic.  If today is January, our expressions will return no data for Prior YTD because there is no date level data.  What the user should really expect?

Same with the QTD  if today is the first month of the Quarter.

MTD simply doesn't make sense in this situation.

You need to talk with the business users to find out what they expect.  Maybe calculate pro-rated amount, e.g. if today is May 5th, it is a 5/31 part of the month, hence the Prior period to date should include 5/31 of the amount for the corresponding month(?)  Just thinking loud...

• ###### Re: Prior Year YTD, Prior Year MTD,Prior Year QTD in Absence of Date Field

Thanks Michael for you suggestion.

I have derived the Flags at the Back end and wrote  a Set Analysis in my Exp. It's working fine.

Thanks for your time and effort.

Regards,

Ravi