Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vikasshana
Creator II
Creator II

YTD Expression not working

Hi,

I've below script.

Data:
Load * Inline [
Date, Sales
01/01/2023, 100
01/10/2023, 200
01/23/2024, 300
01/30/2024, 400
02/21/2024, 500
02/22/2024, 600
02/23/2024, 700
];
 
NoConcatenate
Data1:
Load DateFormated,
     Date,
     MonthYear,
     Year,
     MonthNum,
     Day,
     Quarter,
     Sales,
if(InYearToDate(DateFormated,today(),0),1,0) as YTD,
     if(InMonthToDate(DateFormated,today(),0),1,0) as MTD;
Load Date,
date(date#(Date,'MM/DD/YYYY'),'DD/MM/YYYY') as DateFormated,
//date(Date,'DD/MM/YYYY') as DateFormated1,
//date(MonthStart(date#(Date,'MM/DD/YYYY')),'MMM-YYYY') as MonthYear (or as below code) 
date(date#(Date,'MM/DD/YYYY'),'MMM-YYYY') as MonthYear,
Year(date#(Date,'MM/DD/YYYY')) as Year,
Month(date#(Date,'MM/DD/YYYY')) as Month,
Num(Month(date#(Date,'MM/DD/YYYY'))) as MonthNum,
Day(date#(Date,'MM/DD/YYYY')) as Day,
'Q' & CEIL(NUM(MONTH(date#(Date,'MM/DD/YYYY')))/3) as Quarter,
     Sales
Resident Data;    
 
Drop Table Data;
 
Exit Script;
 
Using below Set Expression to calculate YTD.
Sum({$<DateFormated = {">=$(=Date(YearStart(Today()))) <=$(=Date(Today()))"}>} Sales)
 
Expecting Sum as '1800' but got '2500'. Could you please help me on it.
Labels (2)
1 Solution

Accepted Solutions
theoat
Partner - Creator III
Partner - Creator III

2 expressions can resolve your case :
With the field "Date"
Sum({$<Date = {">=$(=YearStart(Today())) <=$(=Today())"}>} Sales)

With the field "DateFormatted"
Sum({$<DateFormated = {">=$(=Date(YearStart(Today()),'DD/MM/YYYY')) <=$(=Date(Today(),'DD/MM/YYYY'))"}>} Sales)

Enjoy your Qlik.
Kind regards,
Théo ATRAGIE.

View solution in original post

2 Replies
theoat
Partner - Creator III
Partner - Creator III

2 expressions can resolve your case :
With the field "Date"
Sum({$<Date = {">=$(=YearStart(Today())) <=$(=Today())"}>} Sales)

With the field "DateFormatted"
Sum({$<DateFormated = {">=$(=Date(YearStart(Today()),'DD/MM/YYYY')) <=$(=Date(Today(),'DD/MM/YYYY'))"}>} Sales)

Enjoy your Qlik.
Kind regards,
Théo ATRAGIE.

vikasshana
Creator II
Creator II
Author

Thank you so much, it is working as expected.