Filter Date Ranges From Colume

[Eduard23]

Hi Qlik Community,

I Have one column name GL_Date, i need to filter it for specific Date only for year 2023, i need to filter from 10/25/2023 to 11/26/2023, i already try a lot but not working since it always include those all the date.

please help

This the Column Name GL_DATE, I want to write an expression that it will show only those 25/10/2023 to 26/11/2023

i try this expression but not working

 Sum({<GL_DATE={">=$(25/10/2023) <=$(26/11/2023)"}>} ORDER_INVOICE)

[Jebrezov]

Are you using a filter pane or just trying to filter on the gl_date column itself? I can’t visualize the problem at the moment as I don’t fully understand what you are doing and what the result is you are currently getting vs your desired output.

[sidhiq91]

@Eduard23  Could you please tell if you want to filter at the Script level or the sheet level.

At the script level you can always use the where clause like below 

where Date>='11/01/2023' and Date<='11/30/2023';

[Anil_Babu_Samineni]

Perhaps this?

If((GL_Date>='10/25/2023' and GL_Date<='11/26/2023') and Year=2023, GL_Date) as GL_Date_RestrictData

Note, If anything not working, could be date formats are wrong, you have to playaround with that.

Or. If you expected only Last one month as always, you can use AddMonths() function to restrict.

[Eduard23]

i update my post

[Eduard23]

Sum({<GL_DATE={">=$(25/10/2023) <=$(26/11/2023)"}>} ORDER_INVOICE) i try this but still showing other dates

[Eduard23]

error in the expression.

[Jebrezov]

The set expression "Sum({<GL_DATE={">=$(25/10/2023) <=$(26/11/2023)"}>} ORDER_INVOICE)" won't filter the data, it will just sum the Order_invoice data that meets the criteria noted in the set expression. This would be good if you are trying to show a KPI on your sheet on the front end aka sheet view of the app. 

The If statement "If((GL_Date>='10/25/2023' and GL_Date<='11/26/2023') and Year=2023, GL_Date) as GL_Date_RestrictData"  is missing the 'else results'. you probably would need to add a ",0" to the end of it so the expression doesn't error out like this "If((GL_Date>='10/25/2023' and GL_Date<='11/26/2023') and Year=2023, GL_Date,0) as GL_Date_RestrictData". However, this if statement is set up to be used and defined in the load script based on the "as gl_date_restrictdata" addtion. If you want to do this on the front end instead and not the load script, you can drop that part and just add the If statement into a Dimension with the name GL_Date_RestrictData and add this dimension in the filter pane. I would probably change the if statement to be a flag result such as "If((GL_Date>='10/25/2023' and GL_Date<='11/26/2023') and Year=2023, 'Y','N') with a name of 'GL_Date_Flag' or something of the sort so anything within the date range will have a 'Y' and everything outside will have a 'N'. This would simplify the filtering.

[Jeevayswaran]

Hi, Try this:

Sum({<GL_DATE={">=$(=Date(Date#('25/10/2023','DD/MM/YYYY'))<=$(=Date(Date#('26/11/2023','DD/MM/YYYY'))"}>} ORDER_INVOICE)

[TcnCunha_M]

What i can suggest to you:

 Sum({<GL_DATE=, YEAR=, MONTH=,  GL_DATE={">=25/10/2023 <=26/11/2023"}>} ORDER_INVOICE)

In case need to ignore any order selection with cam make this selection not work 

 Sum({1<GL_DATE={">=25/10/2023 <=26/11/2023"}>} ORDER_INVOICE)