Data does not match Month - YEAR filter

[Kakay_Ranas]

Hi!

Thanks in advance to anyone who can help. I'm using a table for my dates, it has 2015-2021. In my Qlik table though, I only want to show up to present date in the selection. I'm getting the correct view but the data does not match. I used below to get MONTH - YEAR, for example I select AUGUST-2019, data from Sept is included unless I manually select dates within the month. I know I did something wrong, appreciate any help to correct it.

=IF(AP_FULLDATE < TODAY (),
Date(MonthStart(AP_FULLDATE),'MMM-YYYY'))

[NadiaB]

Hi @Kakay_Ranas 

What data type is the date value? You might need to truncate the date value:

https://community.qlik.com/t5/QlikView-Scripting/Date-Flooring/m-p/1370625?_ga=2.182782310.196836740...

Kind Regards.

[Kakay_Ranas]

It's varchar

[StarinieriG]

Hi,

try to use Date#

IF(Date#(AP_FULLDATE, 'date format') < TODAY (),
Date(MonthStart(Date#(AP_FULLDATE, 'date format') ),'MMM-YYYY'))

[Kakay_Ranas · Accepted solution]

Thank you all for the feedback. I updated the query instead to remove future dates from my table and that solved it for me.

,DATE_PARAM AS (
SELECT PID_TYPE_ID, PID, PID_START_DATE, PID_END_DATE
FROM OPERATIONS_SANDBOX.OA_ADMIN_MASTER_PID
WHERE PID_TYPE_ID = 1 -- daily
AND PID_START_DATE >= '1/1/2019'
AND PID_END_DATE <= GETDATE()
ORDER BY PID