topic Re: Filter on IF expression qlik sense in App Development

Filter on IF expression qlik sense

joeybird — Mon, 14 Nov 2016 14:32:34 GMT

I have a straight table chart on my sheet

The table chart shows

CustomerID is column 1, column 2 the next shows the following measure

if(COUNT(distinct{$<MonthChart= {"$(='>=' & MonthStart(AddMonths(Max(MonthChart),-0)) & '<=' & Date(Max(EventDate), 'DD/MM/YYYY'))"}>}OrderID) >= 4, 'Yes', 'No')

I just want the column to filter automatically only on CustomerID 'YES'. I can order by Yes or No. I wish I could sort by expression

basically creating a list of Customers of Yes only

please help

Re: Filter on IF expression qlik sense

rittermd — Mon, 14 Nov 2016 14:46:32 GMT

I might suggest that you do this calculation in your load script with a new field that contains the Yes or No.

Then you can either have a filter that allows the user to pick Yes or No or all. Or you can limit what is displayed in the table with set analysis.

You can sort by an expression.

Re: Filter on IF expression qlik sense

joeybird — Tue, 15 Nov 2016 11:52:19 GMT

count does not work in a dataload for this example above

I have tried a rank function with no joy

I have also have the following

If(COUNT(distinct{$<MonthChart= {"$(='>=' & MonthStart(AddMonths(Max(MonthChart),-0)) & '<=' & Date(Max(EventDate), 'DD/MM/YYYY'))"}>}OrderID) >= 4, 0)

I have tried looking under the appearance tab, but I cant seem to keep just the '0' values and not show the nulls

please help

Re: Filter on IF expression qlik sense

shraddha_g — Tue, 15 Nov 2016 11:55:14 GMT

can you share sample data?

Re: Filter on IF expression qlik sense

joeybird — Tue, 15 Nov 2016 12:02:41 GMT

I cant sorry...

just wish there was of way of limiting the values shown in the table shown on the qlik sheet as like the following

Currently shows

CustomerID 4> Repeat Orders in Current month

AAAA 0

BBBB 0

CCCC 0

DDDD Null

EEEE Null

FFFF 0

Wish table on sheet to display as

CustomerID 4> Repeat Orders in Current month

AAAA 0

BBBB 0

CCCC 0

FFFF 0

please help

Re: Filter on IF expression qlik sense

rubenmarin — Tue, 15 Nov 2016 12:35:26 GMT

Hi Joanna, maybe using a calculated dimension for customerID:

Aggr(if(COUNT(distinct{$<MonthChart= {"$(='>=' & MonthStart(AddMonths(Max(MonthChart),-0)) & '<=' & Date(Max(EventDate), 'DD/MM/YYYY'))"}>}OrderID) >= 4, CustomerID), CustomerID)

Re: Filter on IF expression qlik sense

joeybird — Tue, 15 Nov 2016 14:44:43 GMT

Awesome, thanx team x worked a treat