topic Re: Using Set Expression to Filter Data in QlikView

Using Set Expression to Filter Data

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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 — Mon, 06 Nov 2017 21:23:32 GMT

May be as below:

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

swuehl — Mon, 06 Nov 2017 21:26:14 GMT

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

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

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.