Skip to main content
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.