topic Exclude selection in Straight table dimension in QlikView

Exclude selection in Straight table dimension

darkshadow91223 — Wed, 10 Jan 2018 00:03:18 GMT

Hi all!,

I got this straight table and this list box...

and i want to exclude in the table the values that are selected i.e. if Commit and/or CommitBook or Ship were selected then the table will look like this:

PD: Data type its a dimension.

Any idea?

Re: Exclude selection in Straight table dimension

sunny_talwar — Wed, 10 Jan 2018 01:01:16 GMT

Use this in your expression's set analysis

{<[Data Type] = e(Type)>}

I am assuming the dimension field is different from the list box field... if they both are the same, then may be this

{<[Data Type] = e([Data Type])>}

Re: Exclude selection in Straight table dimension

darkshadow91223 — Wed, 10 Jan 2018 01:24:38 GMT

where should i put this?,

"{<[Data Type] = e([Data Type])>} " in the list box?

Re: Exclude selection in Straight table dimension

darkshadow91223 — Wed, 10 Jan 2018 01:45:12 GMT

i already tried to put it in the expression tab in list box and in the table in the dimension in the field "conditional" and nothing, the code turns red

Re: Exclude selection in Straight table dimension

Anil_Babu_Samineni — Wed, 10 Jan 2018 01:50:13 GMT

If you want to remove which is selection in Report then u can call like below for 3 measures

Sum({<[Data Type] = E([Data Type]) >} Measure)

Re: Exclude selection in Straight table dimension

darkshadow91223 — Wed, 10 Jan 2018 02:02:38 GMT

Where should i put that? in the dimension? at the condition field? or list box?

Re: Exclude selection in Straight table dimension

Anil_Babu_Samineni — Wed, 10 Jan 2018 02:04:21 GMT

No, It should be in measure. In report there are 3 measures you should include degrade action to set expression, What are the measures you used?

Re: Exclude selection in Straight table dimension

sunny_talwar — Wed, 10 Jan 2018 09:10:51 GMT

What are the expression you are using for these three columns in your chart? Difference %, Old, New? You need to add the above set analysis to these three expressions

Re: Exclude selection in Straight table dimension

darkshadow91223 — Wed, 10 Jan 2018 14:43:52 GMT

These are the expressions:

Difference % - Num(RangeMax(1 - Sum({<TimeStamp={'Old'}>}Amount) / Sum({<TimeStamp={'New'}>}Amount), 0), '#,###.#0%')

OLD - Sum({<TimeStamp={'Old'}>}Amount)

NEW - Sum({<TimeStamp={'New'}>}Amount)

But i need that the values shown in the chart depends the listbox. The user will feel free to exclude the ones he wants. So it shouldnt be in the dimesion filed (Data Type)? or the list box? (later will be checkboxes)

Thanks for your kick answer stalwar1‌

Re: Exclude selection in Straight table dimension

darkshadow91223 — Wed, 10 Jan 2018 14:47:24 GMT

I dont have any measure 😮 didnt know about it im starting with qlikview.

Could you please explain more?

Thanks for you answer loveisfail‌

Re: Exclude selection in Straight table dimension

sunny_talwar — Wed, 10 Jan 2018 14:47:47 GMT

Just give this a shot

Difference % - Num(RangeMax(1 - Sum({<TimeStamp={'Old'}, [Data Type] = e([Data Type])>}Amount) / Sum({<TimeStamp={'New'}, [Data Type] = e([Data Type])>}Amount), 0), '#,###.#0%')

OLD - Sum({<TimeStamp={'Old'}, [Data Type] = e([Data Type])>}Amount)

NEW - Sum({<TimeStamp={'New'}, [Data Type] = e([Data Type])>}Amount)

or this

Difference % - If(GetSelectedCount([Data Type]) = 0, Num(RangeMax(1 - Sum({<TimeStamp={'Old'}>}Amount) / Sum({<TimeStamp={'New'}>}Amount), 0), '#,###.#0%'), Num(RangeMax(1 - Sum({<TimeStamp={'Old'}, [Data Type] = e([Data Type])>}Amount) / Sum({<TimeStamp={'New'}, [Data Type] = e([Data Type])>}Amount), 0), '#,###.#0%'))

OLD - If(GetSelectedCount([Data Type]) = 0, Sum({<TimeStamp={'Old'}>}Amount), Sum({<TimeStamp={'Old'}, [Data Type] = e([Data Type])>}Amount))

NEW - If(GetSelectedCount([Data Type]) = 0, Sum({<TimeStamp={'New'}>}Amount), Sum({<TimeStamp={'New'}, [Data Type] = e([Data Type])>}Amount))

Re: Exclude selection in Straight table dimension

darkshadow91223 — Wed, 10 Jan 2018 15:10:50 GMT

Thanks a lot Sunny! it work with the second choice but now i got this other problem i didint mention it but i have 2 list box but one is to filter by and the other is to exclude!, but the solution you gave me affects both. is there a way to choose wich listbox will be affected by the solution ?

the information is this:

Filter by:

Field: Data Type

ID objct: LB14

To exclude:

Field: Data Type

ID objct: LB28

Re: Exclude selection in Straight table dimension

sunny_talwar — Wed, 10 Jan 2018 15:14:31 GMT

Does both the list boxes use the same underlying field? If they do... then this won't work... instead create a new field with the same data in the script

Load [Data Type] as [Data Type],

[Data Type] as DataTypeInclude

FROM ...

now when you select DataTypeInclude... it should work fine for you