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

[kishoreravi1983]

Hi Friends,

Please help me to create Prior Year MTD,QTD,YTD.

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({<TYMONTH={'$(vMaxMonth)'}>}TY_NETDOLLARS)/1000000
v$QTD	Sum({<Year={'$(vMaxYear)'},TYQUARTER={'$(vMaxQuarter)'}>}TY_NETDOLLARS)/1000000
v$YTD	Sum({<Year={$(vMaxYear)}>}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.

[Anonymous]

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.

[kishoreravi1983]

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

[Anonymous]

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..

[kishoreravi1983]

Thanks  Michael.

This is what i have done

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

In Resident Table i have written below

if(FDM >= AddYears(YearStart(today()),-1) and FDM <= AddYears(addmonths(MonthEnd(today()),-1),-1),month(FDM)&' '&year(FDM)) as [Prior YTD], 

Stuck for QTD and MTD.

Regards,

Ravi

[Anonymous]

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...

[kishoreravi1983]

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