Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

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

View solution in original post

7 Replies

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

leonling
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?

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?

leonling
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.

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

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

View solution in original post

leonling
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!