Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

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

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

Highlighted
Creator II
Creator II

Highlighted
Contributor III
Contributor III

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