Possible Date Format Issue in YTD

[FlAsh92]

Hey guys, I'm new to Qlik Sense and currently trying to generate a YTD table.

Used guidance from the post below:

https://community.qlik.com/t5/QlikView-Documents/YTQ-QTD-MTD-and-WTD/ta-p/1489245

But the values that populate in the column do not match my data.

Just to give a brief, I inward the Oracle data through a SQL connector. The format of my date field is 'yyyy-mm-dd hh:mm:ss.ssss' and I've labelled it as T_Date. I also get the Year, Month and Day in separate columns which can be used in case the above date format is the issue.

In the Data Load Editor I used the script:

Floor(DateField) AS DateNum

I figured this code separates out the Date part only.

The I moved on to the YTD expression:

Sum({<[T_Date.autoCalendar.Year]=, [T_Date.autoCalendar.Month]=, [T_Date.autoCalendar.Quarter]=, [T_Date.autoCalendar.Week]=, [T_Date.autoCalendar.Date]=, DateNum={">=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum))"}>} T_Value)

I'm not sure if the above expression is right, because it displays the wrong summation value.

Also, I've not defined any specific variables for this.

Please help me and guide me on the issue.

[sunny_talwar · Accepted solution]

Change your variables to the ones in the image and that seems to make them work

[FlAsh92]

@sunny_talwar  Thank you so much!! That fix worked perfectly!!

Just one small additional query...I figured out how to tweak the variable to get MTD too(changed YearStart() to MonthStart()). But is it possible to tweak the same variable/expression to get Previous Year MTD/YTD?!

[sunny_talwar]

For previous year YTD, you can do this

TimeStamp(AddYears(Max(DateField), -1))

and

TimeStamp(YearStart(Max(DateField), -1))

[FlAsh92]

Thank you!!