Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Filter with expression

Hi Everyone,

i try to put this in the table, but it said invalid dimension, please help meSmiley Happy

if(GetFieldSelections(Period)='3 Mths', [Start Date] = {"$(='>=' & Date(today(2), 'D/M/YYYY'))"}, 'D/M/YYYY'), [Start Dates])

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Filter with expression

Try with

=if(GetFieldSelections(Period)='3 Mths', If( [Start Date] >= today(2) and [Start Date] <= AddMonths(today(2),3), [Start Date]))


or

=if(GetFieldSelections(Period)='3 Mths', Aggr(Only({<[Start Date] = {"$(='>=' & Date(today(2), 'D/M/YYYY') &'<=' & Date(AddMonths(today(2),3), 'D/M/YYYY'))"} >} [Start Date]), [Start Date]) )

8 Replies
MVP
MVP

Re: Filter with expression

This part is invalid syntax (partly looks like a field modifier of a set expression, which can only be used in aggregation functions, partly like a format code for a date() function)

[Start Date] = {"$(='>=' & Date(today(2), 'D/M/YYYY'))"}, 'D/M/YYYY')

I assume you want to use this as calculated dimension?

Maybe like

=if(GetFieldSelections(Period)='3 Mths', If( [Start Date] >= today(2), [Start Dates]))


Or using in an expression with an assumed COUNT aggregation function:


=if(GetFieldSelections(Period)='3 Mths',

Count( {< [Start Date] = {"$(='>=' & Date(today(2), 'D/M/YYYY'))"} >} [Start Dates])

)

Not applicable

Re: Filter with expression

Hi Swuehl,

Thank you so much, I have actually create a drop box list, and I would like to connect it to my data:

for example, when I select '3 Mths', the "Start dates" will filter and shows dates that is within next 3 month, but my code fails:

Capture.PNG

if(GetFieldSelections(Period)='3 Mths',[Start Date] = {'$(='>=' & Date(today(2), 'D/M/YYYY') &'<=' & Date(AddMonths(today(2),3), 'D/M/YYYY'))'}

if(GetFieldSelections(Period)='6 Mths',[Start Date] = {'$(='>=' & Date(today(2), 'D/M/YYYY') &'<=' & Date(AddMonths(today(2),6), 'D/M/YYYY'))'}

if(GetFieldSelections(Period)='9 Mths',[Start Date] = {'$(='>=' & Date(today(2), 'D/M/YYYY') &'<=' & Date(AddMonths(today(2),9), 'D/M/YYYY'))'}

if(GetFieldSelections(Period)='1 Yr',[Start Date] = {'$(='>=' & Date(today(2), 'D/M/YYYY') &'<=' & Date(AddMonths(today(2),12), 'D/M/YYYY'))'}

if(GetFieldSelections(Period)='2 Yrs',[Start Date] = {'$(='>=' & Date(today(2), 'D/M/YYYY') &'<=' & Date(AddMonths(today(2),24), 'D/M/YYYY'))'}, [Start Date])))))

MVP
MVP

Re: Filter with expression

Try with

=if(GetFieldSelections(Period)='3 Mths', If( [Start Date] >= today(2) and [Start Date] <= AddMonths(today(2),3), [Start Date]))


or

=if(GetFieldSelections(Period)='3 Mths', Aggr(Only({<[Start Date] = {"$(='>=' & Date(today(2), 'D/M/YYYY') &'<=' & Date(AddMonths(today(2),3), 'D/M/YYYY'))"} >} [Start Date]), [Start Date]) )

Not applicable

Re: Filter with expression

The code works!! It does filter and shows dates that is within next 3 month, but now the start date no longer link with other charts

MVP
MVP

Re: Filter with expression

I don't understand your last statement, if you make a selection in your new filter on a Start Date, I think this should filter also your other charts.

Not applicable

Re: Filter with expression

After filtering on the "Period", there are 4 dates remain.

I have a table contain all the data, thus I am expecting the table will auto filtering based on the remaining start dates, but it does not work. I try to select the 4 start dates, there is error occur.

Capture.PNG  Capture1.PNG

Re: Filter with expression

Would you be able to share your app for troubleshooting purposes

Not applicable

Re: Filter with expression

Hi Sunny,

Thanks for offer the help, I manage to solve the issue. Swuehl's code works wellSmiley Happy

Community Browser