# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

New Contributor II

## How to exclude dates in a Qlik straight table?

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?

Tags (1)
1 Solution

Accepted Solutions
Valued Contributor

## Re: How to exclude dates in a Qlik straight table?

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

7 Replies
MVP

## Re: How to exclude dates in a Qlik straight table?

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

New Contributor II

## Re: How to exclude dates in a Qlik straight table?

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?

MVP

## Re: How to exclude dates in a Qlik straight table?

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

New Contributor II

## Re: How to exclude dates in a Qlik straight table?

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.

MVP

## Re: How to exclude dates in a Qlik straight table?

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

Valued Contributor

## Re: How to exclude dates in a Qlik straight table?

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

New Contributor II

## Re: How to exclude dates in a Qlik straight table?

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

Thank you!