topic Re: Hide and Show $0 using Selection in List Box in QlikView

Hide and Show $0 using Selection in List Box

dmohanty — Tue, 02 Feb 2016 20:44:39 GMT

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!

Re: Hide and Show $0 using Selection in List Box

sunny_talwar — Tue, 02 Feb 2016 20:54:03 GMT

Try the attached application

Include 0s

Exclude 0s

Script:

Table:

LOAD Account,

Invoices,

Qty,

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

FROM

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

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

ListBox:

LOAD * Inline [

Select, NO

Exclude 0, 0

Include 0, 1

];

Straight Table:

Dimension

1) Account

2) Invoices

3) =If(NO = 1, Qty, If(Aggr(Sum(Purchases), Qty) > 0, Qty)) (With 'Suppress When Value Is Null' selected on dimension tab)

Expression

=Sum(Purchases)

Presentation tab

Uncheck 'Supress Zero-Values'

Re: Hide and Show $0 using Selection in List Box

Tue, 02 Feb 2016 20:58:35 GMT

Add script level flag or try below expression

Sum({<Purchase_Amount={"Purchase_Amount>0"}>} Purchase_Amount)

Re: Hide and Show $0 using Selection in List Box

dmohanty — Tue, 02 Feb 2016 21:05:40 GMT

Hey sunindia‌,

Thank you for quick help. It is really good.

Can I have the Qty as it is in the Expression Tab and put some conditions for Invoice field atleast?

Wanted to Hide the Invoice for which Purchase = $0

My Bad- I hadn't changed the Invoice Value for the second Account AA2. Just updated the table -

Account	Invoices	Qty	Purchases
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

Re: Hide and Show $0 using Selection in List Box

sunny_talwar — Tue, 02 Feb 2016 21:16:10 GMT

See if this is what you want

Re: Hide and Show $0 using Selection in List Box

sunny_talwar — Tue, 02 Feb 2016 21:17:56 GMT

Updated app with updated sample

Re: Hide and Show $0 using Selection in List Box

sunny_talwar — Tue, 02 Feb 2016 21:19:33 GMT

Awesome

Re: Hide and Show $0 using Selection in List Box

dmohanty — Tue, 02 Feb 2016 21:20:44 GMT

Oh Great sunindia,

This is what I wanted. Thanks a lot for this help. Got a concept! Kudos to you.

Re: Hide and Show $0 using Selection in List Box

dmohanty — Wed, 03 Feb 2016 01:39:24 GMT

Hi sunindia‌

By any means the If condition taken into script level?

Including it at the chart level, shows a performance issue when the chart has lot more dimensions and data.

Re: Hide and Show $0 using Selection in List Box

sunny_talwar — Wed, 03 Feb 2016 01:50:46 GMT

Calculated dimensions are usually resource intensify. We can look at a expression based solution?

Re: Hide and Show $0 using Selection in List Box

sunny_talwar — Wed, 03 Feb 2016 01:53:14 GMT

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

Re: Hide and Show $0 using Selection in List Box

sunny_talwar — Wed, 03 Feb 2016 01:59:20 GMT

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

Re: Hide and Show $0 using Selection in List Box

Wed, 03 Feb 2016 02:36:25 GMT

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.

Re: Hide and Show $0 using Selection in List Box

dmohanty — Wed, 03 Feb 2016 16:32:22 GMT

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