Using Set Expression to Filter Data

[Anonymous]

I have a table that looks like this:

ID     Date            GateNum

1    1/2/2011                0

1    1/10/2011              1

1    1/2/2018                2

1    1/3/2018                3

1    3/22/2019              4

2    1/2/2011                0

2    1/2/2011                1

And I would like to show a Chart (table) where I filter out all dates that are before today.

ID    Gate 0 Date

1      1/2/2011

2      1/2/2011

Using set expressions, I have to use an agreggation function which I do not want:

This is what I have tried

sum({$<[CPDate]={">=$(=Date(Today(), 'MM/DD/YYYY'))"}>} [CPDate])

which give me the sum of all the dates after today for each ID.

How do I filter out the dates before today?

[trdandamudi]

May be as below:

Count({$<[Date]={"<$(=Date(Today(), 'MM/DD/YYYY'))"}>} [Date])

[swuehl]

A set expression can only be used within an aggregation function.

But you can use aggregation function ONLY(), if you don't like SUM()

The Only Function

You can create your table like this

Use ID and CPDate as dimensions in a straight table and as expression

=Only({$<[CPDate]={"<$(=Date(Today(), 'MM/DD/YYYY'))"}> CPDate)

Then hide the expression column on presentation tab if you don't need it twice.