topic Re: Creating a threshold filter for a table in App Development

Creating a threshold filter for a table

Evan0211 — Tue, 22 Jun 2021 19:06:19 GMT

I have a table that has a field on it with dollar amounts. I would like to create a threshold filter of pre-defined values (All, 100k, 500k, 1M, etc) and when the user selects the amount, the table will filter to the items that are >= the threshold. I cannot find any charts or controls that will do this. I do not have the ability to install any extensions due to restrictions from the data owner.

Thanks!

Re: Creating a threshold filter for a table

jwjackso — Tue, 22 Jun 2021 20:08:03 GMT

You can use the Variable Input with Set Analysis.

Create a variable, vFilter and in the Variable Input define it as

='~All|{<[Dollar Amount]={">100000"}>}~>100k|{<[Dollar Amount]={">500000"}>}~>500k|{<[Dollar Amount]={">1000000"}>}~1M'

Your table expression would look like

Sum($(vFilter)[Dollar Amount])

Re: Creating a threshold filter for a table

Evan0211 — Tue, 22 Jun 2021 20:20:36 GMT

It says Error in Expressions ')' expected

The plain text shows Sum(100000 [Dollar Amount])

Re: Creating a threshold filter for a table

jwjackso — Tue, 22 Jun 2021 20:38:52 GMT

Can you show the string that you used in the variable dynamic expression field? Also replace the [Dollar Amount] with the real field name.

Re: Creating a threshold filter for a table

Evan0211 — Tue, 22 Jun 2021 20:43:15 GMT

I upped the numbers to M from K.

Variable -

='~All|{<[Total Value]={">1000000"}>}~>1M|{<[Total Value]={">5000000"}>}~>5M|{<[Total Value]={">10000000"}>}~10M'

Expression -

Sum($(vFilter)[Total Value])

Re: Creating a threshold filter for a table

jwjackso — Tue, 22 Jun 2021 21:14:44 GMT

With the following data:

Set vFilter="";

Data:
Load * Inline [
Dept,Total Value
D0,100000
D1,1000000
D2,5000000
D3,10000000
D4,20000000
];

Using your variable string:

The variable filter works

Re: Creating a threshold filter for a table

Evan0211 — Tue, 22 Jun 2021 21:24:21 GMT

When I toggle through the buttons, the data does not limit and the column that holds the amounts all turn to "-"

Re: Creating a threshold filter for a table

jwjackso — Tue, 22 Jun 2021 21:26:55 GMT

In the expression editor for Sum($(vFilter)[Total Value]), at the bottom, how has the $(vFilter) been expanded

Re: Creating a threshold filter for a table

Evan0211 — Tue, 22 Jun 2021 21:33:28 GMT

The expression in the column of the table is: =Sum($(vFilter)[Total Value])

The bottom shows: =Sum([TotalValue])

vFilter has not been expanded on the bottom.

Re: Creating a threshold filter for a table

jwjackso — Tue, 22 Jun 2021 21:35:28 GMT

And you have selected something other than the All button?

Re: Creating a threshold filter for a table

Evan0211 — Tue, 22 Jun 2021 21:37:30 GMT

I just selected 1M and the expression at the bottom of the editor is:

=Sum({<[Total Value]=[">1000000"}>][Total Value])

Re: Creating a threshold filter for a table

jwjackso — Tue, 22 Jun 2021 21:43:13 GMT

Did you cut and paste the original filter from this discussion? It looks like there is embedded html characters, <

You should be seeing

=Sum({<[Total Value={">1000000"}>}[Total Value])

It also looks like there is are square braces "[]" instead of a curly "{}" braces

Re: Creating a threshold filter for a table

Evan0211 — Tue, 22 Jun 2021 21:44:11 GMT

Let me sanitize it real quick and see if that clears out the issue

Re: Creating a threshold filter for a table

Evan0211 — Tue, 22 Jun 2021 21:49:03 GMT

I ran it through notepad and it still comes back with the #< and > encoding. Any ideas?

Re: Creating a threshold filter for a table

jwjackso — Tue, 22 Jun 2021 21:56:48 GMT

I can't replicate the encoding, I can only suggest manually typing the string in the Qlik expression editor.

Re: Creating a threshold filter for a table

Evan0211 — Tue, 22 Jun 2021 22:10:59 GMT

I just went back and hand typed it and it still shows the encoding.

Re: Creating a threshold filter for a table

Evan0211 — Wed, 23 Jun 2021 13:38:38 GMT

I spent most of the night trying to get this encoding issue cleared up and have run out of ideas. I have created new variables, I have hand typed the expression, I have run it through notepad to strip anything out of it, I have even tried online encoding/decoding. Nothing seems to work. No matter what, I have this as my expression:

=Sum({<[Total Value]={">1000000"}>}[Total Value])

The error says Error in expression: '}' expected

This is my dynamic value expression:

='~All|{<[Total Value]={">100000"}>}~>100k|{<[Total Value]={">500000"}>}~>500k|{<[Total Value]={">1000000"}>}~1M'

Re: Creating a threshold filter for a table

jwjackso — Wed, 23 Jun 2021 13:37:16 GMT

What browser and qlik sense version are you using?

Re: Creating a threshold filter for a table

Evan0211 — Wed, 23 Jun 2021 13:41:34 GMT

I am using chrome. I am unsure on the version, I am unfortunately just a cog in the wheel on this. I don't own any of it.

Re: Creating a threshold filter for a table

Evan0211 — Wed, 23 Jun 2021 14:20:19 GMT

Would there be any reason I would need an escape sequence with the quotes?