Exclude Filter from Affecting Chart when filter dimension is an expression

[Anonymous]

Hi All,

In my date filter field, i am displaying only the latest closed month (Idea is to toggle between full year view and recent month view).

i am using an expression as follows:

=if(Month(Closeddate) = vMaxMonth and Year(Closeddate) = vMaxYear, vMaxMonth)

I have created variables vMaxMonth & vMaxYear in the load script to get the month and year of the maximum closed date.

Now, my requirement is that i dont want this filter to affect one chart alone. Hence i give the expression as:

Count ({<Closeddate =>} [Application ID])

But this does not work. If i had displayed all closed dates in the filter instead of the expression i have used, the above works.

Is this a property of Qlik or do i need to use a different expression in place of the above to ensure that the filter is not considered?

Yours Truly,

S.Manikantan

[joseph_eftamand]

I think this is working the way you describe. Let me know if this works for you.

Regards,

Joseph

[Anonymous]

The file shows as empty when i open using Qlik Sense Desktop. Can you share the QVF file if possible? Else, please put out the solution in text.

[joseph_eftamand]

Sorry I put that together in QlikView 

Script is the same though:

TABLE1:

LOAD * INLINE [

    Value, ClosedDate

    1, 02/05/2018

    2, 03/05/2018

    3, 04/05/2018

    4, 05/05/2018

    5, 06/05/2018

    6, 06/05/2018

    7, 08/05/2018

    8, 09/05/2018

    9, 09/06/2018

    10, 09/06/2018

    11, 12/06/2018

    12, 13/06/2018

    13, 14/07/2018

    14, 15/07/2018

];

Table:

Load

Value,

Month(ClosedDate) as ClosedMonth,

Year(ClosedDate) as ClosedYear,

ClosedDate

Resident TABLE1

;

DROP Table TABLE1;

Temp:

LOAD

     Max(ClosedDate) as Maxcd

Resident Table;

LET vMaxDate = (peek('Maxcd'))w;

LET vMaxYear = Year(vMaxDate);

LET vMaxMonth = Month(vMaxDate);

LET vMaxmy = vMaxMonth & '-' & vMaxYear;

DROP TABLE Temp;

FilterTable:

Load

ClosedDate,

Month(ClosedDate) as FilterMonth

Resident Table

Where ClosedDate = $(vMaxDate)

;

Chart Expression:

Count({<ClosedDate =, ClosedMonth =, ClosedYear =, FilterMonth = >}DISTINCT Value)

Use Field FilterMonth as the filter.

[Anonymous]

Hi Joseph,

Hitting a road block over the where query - a date format issue apparently. I am getting zero matching records out of the where clause.

LET vMaxDate = peek('Maxcd');

Closed date is of Date-Time Format (01/01/1900 00:00:00). I have converted the format to the required one. Still comparison not going well. Can you take a look into this?

where Date([Closeddate],'MM/DD/YYYY') = Date($(vMaxDate)','MM/DD/YYYY');

Tried various permutations and combinations for the date and variable format.

[joseph_eftamand]

Are you sure timestamp is interpreted as a number? Bring it up in a text box, are the numbers indented to the LEFT? QS is then reading it as a text.

Try doing this Timestamp#(Time,'MM/DD/YYYY hh:mm:ss') as TimeStamp

To convert to proper timestamp beforehand in your first load statement.

Regards,

Joseph

[Anonymous]

Hi Joseph, Spot on in terms of the Time Stamp.

More importantly, the functionality is working as expected.

Thanks for your timely help.