Hide and Show $0 using Selection in List Box

[dmohanty]

Hi All,

I have a chart like this below -

Account	Invoices	Qty	Purchases
AA1	100	20	$100
AA1	101	40	$0
AA1	102	10	$20
AA2	200	50	$80
AA2	200	35	$60
AA2	200	25	$0
AA2	200	45	$0

Requirement -

I need to create a List Box called - 'Hide $0 Invoices' and having two values - Exclude and Include.

When Exclude will be selected - the rows having Purchases as $0, will be hidden.

When Include will be selected - the rows having Purchases as $0, will be visible.

By default the selection should be at Exclude.

Please help!

[sunny_talwar]

See this one, I will see if there are other solutions

[sunny_talwar]

Not sure if you need to use Sum() or not, but if Avg() can work then you can try this:

Table:

LOAD Account,

    Invoices,

    Qty,

    Money#(Purchases, '$###') as Purchases,

    If(Money#(Purchases, '$###') > 0, Money#(Purchases, '$###')) as Purchases2

FROM

[https://community.qlik.com/thread/203929]

(html, codepage is 1252, embedded labels, table is @2);

ListBox:

LOAD * Inline [

Select, NO

Exclude 0, 0

Include 0, 1

];

Expression 1: =If(Len(Trim(Column(2))) > 0, Sum(Qty))

Expression 2: =If(NO = 1, Avg(Purchases), Avg(Purchases2))

[#4 · Accepted solution]

You can simply tweak the data model and add the flag table to data model. Then you don't need any complex expressions on UI.

Data:

LOAD

  *,

  If(Purchase=0,0,1) AS _Flag

;

LOAD * Inline [

Account,Invoice,Qty,Purchase

AA1,100,20,100

AA1,101,40,0

AA1,102,10,20

AA2,200,50,80

AA2,201,35,60

AA2,202,25,0

AA2,203,45,0

];

_FlagTable:

LOAD * Inline [

_Flag , SelectName

1, Include 0

0, Include 0

1, Exclude 0

];

Create the Straight table with 3 dims & 1 expression.

Please find the attached sample qvw.

[dmohanty]

Hey dathu.qv‌,

Its a great trick with work with Flag. You approach surely helped in achieving the results and overcoming the performance issues in charts with disturbing the existing chart functionalities.

I will practice to put the Flag concept going forward. Great help