MTD and YTD set analysis when financial year starts in April

[vinayk1]

Hi I have 'Data_month' as my date field, please help me with set analysis expressions for YTD  and MTD when fiscal year starts  from april

basically i need to show ytd and mtd on selection of month

[Or]

All of Qlik's YTD type functions take a "First month of year" parameter, e.g.

InYearToDate (timestamp, base_date, period_no[, first_month_of_year])

[vinayk1]

in what format first year of month to me menitoned like APR OR 03

[Or]

I would assume 4.

https://help.qlik.com/en-US/qlikview/May2022/Subsystems/Client/Content/QV_QlikView/Scripting/DateAnd...

[vinayk1]

can anyone help please?

[aveeeeeee7en]

Hi @vinayk1,

You can use @Or suggested solution by creating YTD & MTD flags in backend script.

or, you can use Set analysis.

Here are the expressions:

YTD (till Max selected Date):
=Sum({<Data_month=, Data_month={"$(='>='&Date(YearStart(Max(Data_month),0,4),'DD/MM/YYYY') & '<='&Date(Max(Data_month),'DD/MM/YYYY'))"}>} Sales)

YTD (till Monthend Max selected Date)
=Sum({<Data_month=, Data_month={"$(='>='&Date(YearStart(Max(Data_month),0,4),'DD/MM/YYYY') & '<='&Date(MonthEnd(Max(Data_month)),'DD/MM/YYYY'))"}>} Sales)

YTD (Complete Year):
=Sum({<Data_month=, Data_month={"$(='>='&Date(YearStart(Max(Data_month),0,4),'DD/MM/YYYY') & '<='&Date(YearEnd(Max(Data_month),0,4),'DD/MM/YYYY'))"}>} Sales)

MTD (till Max selected Date):
=Sum({<Data_month1=, Data_month1={"$(='>='&Date(MonthStart(Max(Data_month)),'DD/MM/YYYY') & '<='&Date(Max(Data_month),'DD/MM/YYYY'))"}>} Sales1)

MTD (Complete Month):
=Sum({<Data_month1=, Data_month1={"$(='>='&Date(MonthStart(Max(Data_month)),'DD/MM/YYYY') & '<='&Date(MonthEnd(Max(Data_month)),'DD/MM/YYYY'))"}>} Sales1)

Result:

Use expressions as per your requirement.

Regards,
Av7eN

[vikasmahajan]

Hi ,

Please refer my post created flag logic you can implement the same.

https://community.qlik.com/t5/Member-Articles/As-of-Table-with-YTD-MTD-QTD-amp-Calculation-of-Rollin...

& find script master calendar script for FY April:

TEMP:
LOAD Max("Posting Date") AS MaxDate
from [lib://3_Qvds/FactData.qvd] (qvd);

LET varMaxDate = Num(peek('MaxDate', 0, 'TEMP'));

TEMP1:
LOAD Min("Posting Date") AS MinDate
from [lib://3_Qvds/FactData.qvd](qvd);

LET varMinDate = Num(peek('MinDate', 0, 'TEMP1'));

//*************** Temporary Calendar ***************
TempCalendar:
LOAD
$(varMinDate)+IterNo()-1 AS Num,
Date($(varMinDate)+IterNo()-1) AS TempDate
AUTOGENERATE 1 WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

//*************** Master Calendar ***************

MasterCalendar:
LOAD TempDate AS [Posting Date],
TempDate,
TempDate AS %Date,
YearName(TempDate,0,4) AS FinancialYear,
Date(Yearstart(TempDate,0,4)) AS YearStart,
Date(Yearend(TempDate,0,4)) AS YearEnd,
Num(Date(Yearstart(TempDate,0,4),'YYYY')) AS FinancialYearNo,
date(MonthStart(TempDate),'MMM-YY') AS MonthYear,
Month(TempDate) AS MonthName,
If(Num(Month(TempDate))>3,Num(Month(TempDate))-3,Num(Month(TempDate))+9) AS MonthNumber,
MonthStart(TempDate) AS MonthStart,
MonthEnd(TempDate) AS MonthEnd,
Year(TempDate) AS Year,
'Q' & alt(if(Month(TempDate)<4,4),if(Month(TempDate)<7,1),if(Month(TempDate)<10,2),3) AS Quarter,
quarterstart(TempDate,0,4) AS QuarterStart,
quarterend(TempDate,0,4) AS QuarterEnd,
quartername(TempDate,0,4) AS QuarterName,
if(Num(Month(TempDate))>3,Year(TempDate)+1,Year(TempDate)) AS FiscalYear,
if(Num(Month(TempDate))>3,Year(TempDate)+1,Year(TempDate))-1 as P_FiscalYear ,
AutoNumber(Month(TempDate) & Year(TempDate)) AS Sequential,
NUM(TempDate) AS DateNum,
monthname(TempDate) AS CalMonthYear,
Day(TempDate) &'-'& Date(TempDate,'MMM') AS DayMon,
num(Year(TempDate)&num(Month(TempDate),'00')) as YEAR_MONTH, // Link between tables for previous month / year selection
num((Year(TempDate)-1)&num(Month(TempDate),'00')) AS YEAR_MONTH_PREVIOUS, // Link between tables for previous month / year selection
num((Year(TempDate)-2)&num(Month(TempDate),'00')) AS P_YEAR_MONTH_PREVIOUS2,
num((Year(TempDate)-3)&num(Month(TempDate),'00')) AS P_YEAR_MONTH_PREVIOUS3,
num((Year(TempDate)-4)&num(Month(TempDate),'00')) AS P_YEAR_MONTH_PREVIOUS4,
num((Year(TempDate)-5)&num(Month(TempDate),'00')) AS P_YEAR_MONTH_PREVIOUS5,
Date(TempDate,'MMM') &'-'& Date(TempDate,'YY') as YearMonth,
//quarterstart(TempDate,0,4) &'-'& Date(TempDate,'YY') as YearQtr
'Q' & alt(if(Month(TempDate)<4,4),if(Month(TempDate)<7,1),if(Month(TempDate)<10,2),3) &'-'&right(Date(TempDate,'YY'),2) as YearQtr,
'FY' & YearName(TempDate,0,4) as ChartFinYear
RESIDENT TempCalendar
ORDER BY TempDate Asc;

DROP Table TempCalendar;
DROP Table TEMP;
DROP Table TEMP1;

[vinayk1]

Thanks for the response, 

I had a question does MTD factor in  april to be first month of the year.

[aveeeeeee7en]

Hi @vinayk1 

Try this:

YTD (till Max selected Date):
=Sum({<Data_month={"$(='>='&Date(YearStart(Max(Data_month),0,4),'DD/MM/YYYY') & '<='&Date(Max(Data_month),'DD/MM/YYYY'))"}>} Sales)

YTD (till Monthend Max selected Date)
=Sum({<Data_month={"$(='>='&Date(YearStart(Max(Data_month),0,4),'DD/MM/YYYY') & '<='&Date(MonthEnd(Max(Data_month)),'DD/MM/YYYY'))"}>} Sales)

YTD (Complete Year):
=Sum({<Data_month={"$(='>='&Date(YearStart(Max(Data_month),0,4),'DD/MM/YYYY') & '<='&Date(YearEnd(Max(Data_month),0,4),'DD/MM/YYYY'))"}>} Sales)

MTD (till Max selected Date):
=Sum({<Data_month={"$(='>='&Date(MonthStart(Max(Data_month)),'DD/MM/YYYY') & '<='&Date(Max(Data_month),'DD/MM/YYYY'))"}>} Sales1)

MTD (Complete Month):
=Sum({<Data_month={"$(='>='&Date(MonthStart(Max(Data_month)),'DD/MM/YYYY') & '<='&Date(MonthEnd(Max(Data_month)),'DD/MM/YYYY'))"}>} Sales1)

Regards,

Av7eN