How to exclude dates in a Qlik straight table?

[QlikToFindOut]

Hello,

I have a straight table created that has a particular filter for each column. For example, the table is only supposed to show the following for lines of business:

=aggr(only({<[Opportunity LOB] = {'China'
                                , 'Czech Republic'
                                , 'Korea'
                                , 'Mexico'
                                , 'UK'}>}
[Opportunity LOB]), [Opportunity LOB])

This will filter the table such that the [Opportunity LOB] will only include the values above.

However, I'm at a bit of a loss when it comes to the date.

Normally, I would write something like below:

=sum({[Opportunity Date] = {">=$(=num('2017-09-30'))"}> } [Revenue])

However, there is no math involved in this. I only want the column to show all dates greater than that inside the set analysis. How can this be done?

[sunny_talwar]

I think you can try this

Dimension

[Opportunity LOB]

Expression

=Sum({<[Opportunity Date] = {">=$(=num('2017-09-30'))"}, [Opportunity LOB] = {'China', 'Czech Republic', 'Korea', 'Mexico','UK'}>} [Revenue])

[QlikToFindOut]

Sorry, those were only examples up above.

I meant to ask how I can write a set analysis function to show all dates greater than September 30, 2017 in which [Opportunity Date] would be the only field used. As if [Opportunity Date] was the ONLY field in the straight table, how can I restrict it such that the column would show all dates greater than September 30, 2017?

[sunny_talwar]

I believe the above should work... if you Opportunity Date is in number format, is it in number format?

[QlikToFindOut]

I appreciate the help, but the output would be something other than a date. Basically, I'm trying to create the following table (which is only one column):

Date
2017-09-30
2017-10-30
2017-11-27
2017-11-30
2017-12-15

I want to write an expression (it might not even be set analysis) such that the column would only show all dates greater than a certain date. The other fields are irrelevant. The only field that's relevant is [Opportunity Date]. Unless what I'm trying to do is impossible.

[sunny_talwar]

Is this a list box object? You can try this

=Aggr(Only({<[Opportunity Date] = {">=$(=num('2017-09-30'))"}, [Opportunity LOB] = {'China', 'Czech Republic', 'Korea', 'Mexico','UK'}>} [Opportunity LOB]), [Opportunity LOB])

[chrismarlow · Accepted solution]

The Aggr I would only see working in a List Box (rather than Straight Table) - with dates formatted UK style =aggr(only({<[Opportunity Date]={">=30/09/2017"}>}[Opportunity Date]),[Opportunity Date]) in the expression works for me.

There might be another way of doing it in Straight Table but I think you have to have a dimension (so you would use Opportunity Date) & then have a single expression that you hide (on presentation tab), to give the impression of a single column, again with dates formatted UK style only({<[Opportunity Date]={">=30/09/2017"}>}[Opportunity Date]).

[QlikToFindOut]

Exactly what I was looking for and it turns out I already had it done.

Thank you!