Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kishoreravi1983
Contributor III
Contributor III

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

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$QTDSum({<Year={'$(vMaxYear)'},TYQUARTER={'$(vMaxQuarter)'}>}TY_NETDOLLARS)/1000000
v$YTDSum({<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.

6 Replies
Anonymous
Not applicable

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
Contributor III
Contributor III
Author

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
Not applicable

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
Contributor III
Contributor III
Author

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
Not applicable

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
Contributor III
Contributor III
Author

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