Discussion board where members can learn more about Qlik Sense App Development and Usage.
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.
But stuck in Creating MTD, QTD YTD for Previous Year
It would be great help if some one can through some pointers.
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.
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..
Your month format is FYYYYY-MM, so to get the start of the month will be
or even shorter
Both will return a full date, the first day of the month..
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.
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...
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.