Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Talend Cloud AWS EU Scheduled Outage: Starting Tues 26 May 21:00 CEST with expected completion Wed 27 May 01:00 CEST
cancel
Showing results for 
Search instead for 
Did you mean: 
TrineBP
Contributor
Contributor

Calculate current YTD and prior YTD

I have create a master calendar using the data load editor. See below. 

My question is how to calculate current YTD and prior YTD as master measure with below master calendar. 

 

QuartersMap:
MAPPING LOAD
RowNo() as Month,
'Q' & Ceil (RowNo()/3) as Quarter
AutoGenerate(12);

Temp:
Load
min("Posting Date") as minDate,
max("Posting Date") as maxDate
Resident Entry;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, '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 "Posting Date",
Week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
ApplyMap('QuartersMap', Month(TempDate), Null()) as Quarter,
Week(WeekStart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay,
Left(Month(TempDate),3)&'-'&Right(Year(TempDate),2) as MonthYear
RESIDENT TempCalendar
ORDER BY TempDate ASC;

DROP TABLE TempCalendar;
DROP TABLE Temp;

 

Labels (1)
1 Reply
PrashantSangle

https://community.qlik.com/t5/New-to-Qlik-Sense/Set-Analysis-YTD-Compared-with-Previous-Years-Same-P...

 

Check this thread and modify field name as per your fieldname

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂