topic Re: Filter Date Ranges From Colume in Visualization and Usability

Filter Date Ranges From Colume

Eduard23 — Mon, 27 Nov 2023 11:14:16 GMT

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)

Re: Filter Date Ranges From Colume

Jebrezov — Sun, 26 Nov 2023 03:16:56 GMT

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.

Re: Filter Date Ranges From Colume

sidhiq91 — Sun, 26 Nov 2023 12:46:13 GMT

@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';

Re: Filter Date Ranges From Colume

Anil_Babu_Samineni — Sun, 26 Nov 2023 20:36:36 GMT

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.

Re: Filter Date Ranges From Colume

Eduard23 — Mon, 27 Nov 2023 11:14:30 GMT

i update my post

Re: Filter Date Ranges From Colume

Eduard23 — Mon, 27 Nov 2023 11:15:13 GMT

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

Re: Filter Date Ranges From Colume

Eduard23 — Mon, 27 Nov 2023 14:24:36 GMT

error in the expression.

Re: Filter Date Ranges From Colume

Jebrezov — Mon, 27 Nov 2023 14:51:05 GMT

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.

Re: Filter Date Ranges From Colume

Jeevayswaran — Tue, 28 Nov 2023 07:41:36 GMT

Hi, Try this:

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

Re: Filter Date Ranges From Colume

TcnCunha_M — Tue, 28 Nov 2023 09:24:40 GMT

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)

Re: Filter Date Ranges From Colume

Eduard23 — Fri, 26 Jan 2024 01:35:07 GMT

yes want to filter the date in script level