topic Re: YTD not working as expected. in QlikView

YTD not working as expected.

vikasshana — Thu, 22 Feb 2024 08:06:42 GMT

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.

Re: YTD not working as expected.

theoat — Thu, 22 Feb 2024 10:16:14 GMT

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.

Re: YTD not working as expected.

JonnyPoole — Thu, 22 Feb 2024 13:38:05 GMT

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()))"