topic Re: Make a listbox with excluding values in QlikView

Make a listbox with excluding values

prat1507 — Wed, 25 Nov 2020 16:16:04 GMT

hi All

My query is if I've a listbox say Fruits

Fruits:

Apple

Banana

Pineapple

Mango

Strawberry

I want to Create a listbox as:

Fruits:

Apple

Banana

Pineapple

Mango

Strawberry

Not Mango

If someone selects Not Mango, it must reflect in all the charts showing + I must be able to select Not Mango and Banana together.

Any kind of help will be appreciated. Thanks in advance.

Regards
Pratyush

Re: Make a listbox with excluding values

techvarun — Mon, 24 Jul 2017 08:16:59 GMT

Please try the attached

Re: Make a listbox with excluding values

techvarun — Mon, 24 Jul 2017 08:17:47 GMT

Expression used : =aggr(Only({1-$} Fruits),Fruits)

Re: Make a listbox with excluding values

ahaahaaha — Mon, 24 Jul 2017 08:23:11 GMT

Hi Pratyush,

May be like at attached file?

Regards,

Andrey

Re: Make a listbox with excluding values

prat1507 — Mon, 24 Jul 2017 08:47:10 GMT

Hi Andrey, I dont want to make changes to the charts but handle it through the list box only. There are too many charts and it is not possible to change the expression everywhere.

Regards
Pratyush

Re: Make a listbox with excluding values

prat1507 — Mon, 24 Jul 2017 10:11:05 GMT

Any other solution?

Re: Make a listbox with excluding values

Anil_Babu_Samineni — Mon, 24 Jul 2017 10:16:50 GMT

Where this "Non Mango" coming from? Does this same table / diff table?

I assume, You need some charts displaying. Can you describe more how you want to show even selection from filter

Re: Make a listbox with excluding values

prat1507 — Mon, 24 Jul 2017 10:29:49 GMT

So No Mango represents every other value than Mango. Lets' say if I have data as

Fruits, Trees

Apple, 10

Banana, 20

Pineapple, 30

Mango, 40

Strawberry, 50

I want to create a listbox such that it contains the below values

Apple

Banana

Pineapple

Mango

Strawberry

No Mango

If someone selects No Mango in the filter, the expression Sum(Trees) would return

110 (150-40)

If No Mango is selected, one should be able to select the other value ex. Apple

it means No Mango and Apple are selected together

and it should show the Sum as 10.

Regards
Pratyush

Re: Make a listbox with excluding values

Kushal_Chawda — Mon, 24 Jul 2017 10:43:03 GMT

I think you need to write some script to achieve this

Data:

LOAD * Inline [

Fruits, Trees

Apple, 10

Banana, 20

Pineapple, 30

Mango, 40

Strawberry, 50];

Concatenate

LOAD 'No Mango' as Fruits,

Trees

Resident Data

where Fruits<>'Mango';

Now you can use the Fruits in lisbox

Re: Make a listbox with excluding values

arvind_patil — Mon, 24 Jul 2017 10:52:42 GMT

Hi Pratyush,

Please find the attached app.

Thanks,

Arvind Patil

Re: Make a listbox with excluding values

rahulpawarb — Mon, 24 Jul 2017 11:05:17 GMT

Hello Pratyush,

Trust that you are doing well!

Please refer below as workaround:

Script:

Data:
LOAD * INLINE [
Fruits, Trees
Apple, 10
Banana, 20
Pineapple, 30
Mango, 40
Strawberry, 50
];
Concatenate
LOAD 'No Mango' AS Fruits,
            Sum(Trees) AS Trees
Resident Data
Where Fruits <> 'Mango';

KPI Exression:

='Trees: ' & If(GetCurrentSelections() ='Fruits: No Mango', Sum(Trees), Sum({<Fruits-={'No Mango'}>}Trees))

Also review the sample application attached herewith.

Regards!

Rahul

Re: Make a listbox with excluding values

prat1507 — Mon, 24 Jul 2017 11:12:20 GMT

Hi Rahul

Thanks for your reply, but this is not what I'm looking for. I cant state this for a single KPI, it is being used for multiple KPIs (on multiple charts as well). This would work fine if I've a single expression. Please let me know if what can I do to make it work everywhere.

Regards
Pratyush

Re: Make a listbox with excluding values

prat1507 — Mon, 24 Jul 2017 11:14:20 GMT

Hi Arvind

I want everything under a single filter.

Regards
Pratyush

Re: Make a listbox with excluding values

prat1507 — Mon, 24 Jul 2017 11:15:23 GMT

Hi Kushal

Regards
Pratyush

Re: Make a listbox with excluding values

arvind_patil — Mon, 24 Jul 2017 11:29:28 GMT

Hi Pratyush,

Please Find the Attachment.

Thanks

Arvind Patil

Re: Make a listbox with excluding values

arvind_patil — Mon, 24 Jul 2017 11:30:38 GMT

Here Two solutions of your probelm.

Thanks,

Arvind Patil

Re: Make a listbox with excluding values

prat1507 — Mon, 24 Jul 2017 11:32:07 GMT

Hi Arvind

Regards
Pratyush

Re: Make a listbox with excluding values

rahulpawarb — Mon, 24 Jul 2017 11:44:27 GMT

Hello Pratyush,

As a workaround, you can add a Flag field (-1 for No Mango else 1) - Refer below script:

Data:
LOAD *, 1 AS Flag INLINE [
Fruits, Trees
Apple, 10
Banana, 20
Pineapple, 30
Mango, 40
Strawberry, 50
];
Concatenate
LOAD 'No Mango' AS Fruits,
            Sum(Trees) AS Trees,
            -1 AS Flag
Resident Data
Where Fruits <> 'Mango';

Modify the existing expression(s) as:

= If(Flag=-1, Sum(Trees), Sum({<Fruits-={'No Mango'}>}Trees))

Hope this will help.

P.S.: Create variables to reduce the maintenance of expressions at various charts & KPIs level.

Regards!

Rahul Pawar

Re: Make a listbox with excluding values

arvind_patil — Mon, 24 Jul 2017 11:58:00 GMT

Hi Pratyush,

I think That will be solution you need to apply every where.

Thanks,

Arvind Patil

Re: Make a listbox with excluding values

passionate — Mon, 24 Jul 2017 12:01:24 GMT

Hi Pratyush,

PFA, Solution.

Regards,

Pankaj