Filter with expression

[#1]

Hi Everyone,

i try to put this in the table, but it said invalid dimension, please help me:)

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

[swuehl]

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])

)

[#3]

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:

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])))))

[swuehl · Accepted solution]

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]) )

[#5]

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

[swuehl]

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.

[#7]

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.

 

[sunny_talwar]

Would you be able to share your app for troubleshooting purposes

[#9]

Hi Sunny,

Thanks for offer the help, I manage to solve the issue. Swuehl's code works well:)