Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

pradnyan
New Contributor II

Fiscal Year YTD,MTD,WTD,QTD

i want to find  YTD,MTD,WTD,QTD using Fiscal Year,Fiscal Month,Fiscal Week,Fiscal Quarter

Please give Solution for Following Data.

I need to count(ID) for fiscal year,Fiscal Month,Fiscal Week,Fiscal Quarter

Below is the sample date and id .Pls help asap

<   

IDDATE
E112/06/2017 04:45 PM
E212/07/2017 01:15 PM
E329/07/2017 09:15 AM
E429/07/2017 02:25 PM
E521/07/2017 06:50 AM
E613/07/2017 07:40 PM
E701/12/2017 06:30 AM
E802/12/2017 08:00 AM
E901/12/2017 07:40 AM
E1001/12/2017 01:00 PM
E1103/12/2017 01:10 PM
E1203/12/2017 04:10 PM
E1304/12/2017 04:15 PM
E1401/12/2017 03:05 PM
E1501/12/2017 06:00 AM
E1603/12/2017 03:40 PM
E1704/12/2017 05:00 PM
E1805/12/2017 01:50 PM
E1903/01/2018 05:50 AM
E2003/01/2018 07:00 AM
E2103/01/2018 02:25 AM
E2203/01/2018 02:40 AM
E2301/02/2018 10:35 AM
E2402/02/2018 10:35 AM
E2503/02/2018 10:35 AM
E2604/02/2018 10:35 AM
E2705/02/2018 10:35 AM
E2806/02/2018 10:35 AM
3 Replies
omarbensalem
Esteemed Contributor

Re: Fiscal Year YTD,MTD,WTD,QTD

1st :

In this table; create a date field :

YourTable;

Load * ,

date(Date#(Subfield(DATE,' ',1),'DD/MM/YYYY')) as DateField

from source;

Then create a master Calendar as follow:

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12); 

 

Temp: 

Load 

               min(DateField) as minDate, 

              max(DateField) as maxDate 

Resident YourTable

 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

 

TempCalendar: 

LOAD 

               $(varMinDate) + Iterno()-1 As Num, 

             Date($(varMinDate) + IterNo() - 1) as TempDate 

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

 

MasterCalendar: 

Load 

               TempDate AS DateField

               week(TempDate) As Week, 

               Year(TempDate) As Year, 

              Month(TempDate) As Month, 

              Day(TempDate) As Day, 

               YeartoDate(TempDate)*-1 as CurYTDFlag, 

               YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

               WeekDay(TempDate) as WeekDay 

Resident TempCalendar 

Order By TempDate ASC; 

Drop Table TempCalendar; 


Then please refer to this link to create ur YTD MTD WTD QTD expressions (I tried to explain how to do it step by step)

YTD, MTD issue

qlik4asif
Contributor II

Re: Fiscal Year YTD,MTD,WTD,QTD

pradnyan
New Contributor II

Re: Fiscal Year YTD,MTD,WTD,QTD

Please find attached table and kindly provide Fiscal year's YTD, MTD,WTD,QTD logic for the same given data in query