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 not working as expected.

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 (3)
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.

JonnyPoole
Employee
Employee

I think its because this date and data  is actually tomorrow 

02/23/2024, 700

it falls outside your set analysis modifier, if you run it today, February 22 2024. 

">=$(=Date(YearStart(Today()))) <=$(=Date(Today()))"

 

JonnyPoole_0-1708609078276.png