topic Pass selections to data island table in QlikView

Pass selections to data island table

QlikDev001 — Mon, 05 Jul 2021 10:57:47 GMT

Hi Geeks

Need your help. I want to pass the selections in the dashboard to a data island table.

For eg, I have Region, Product and Year to pass to data island table.

I am doing something like this. Sum({<RegionIsland = {GetFieldSelections(Region)}, ProductIsland = {GetFieldSelections(Product)}, Year = {GetFieldSelections(Year)}>}Sales)

This works fine if all the 3 selections are made. But I want to include the dynamic comma(,) in between the three selections. If only one field has selections, I can use an if condition and make the set analysis null but the comma is the problem.

So, in brief Sum(a,b,c) and sometimes only a is available, sometimes only b, a and b, a and b and c. I want to derive this through variables.

Any help is highly appreciated.

Regards

Re: Pass selections to data island table

PiEye — Mon, 05 Jul 2021 15:52:57 GMT

Hi QlikDev

Basically, for each category you need a comma delimited list of selected items. These will also need to be separated by double quotes.

EG RegionIsland = {"a","b","c"}

Where a, b, and c are the regions selected in the main data model.

The Concat() function can achieve this. This is a string function which appends all the available values in a list, into a single string. The second parameter is the delimiter.

Create a variable called vRegionSelected and set the value to

=concat(distinct '"' & Region & '"' , ',' )

This will produce the string that looks like "a","b","c"

The variable can then be referenced in set analysis using the $() operator, which turns it into code, implanting the string into your expression.

Sum({<RegionIsland = {$(vRegionSelected)},....

Does this work?

Re: Pass selections to data island table

QlikDev001 — Mon, 05 Jul 2021 16:12:55 GMT

Thanks for the response. For single field, its ok. I have 3 fields and I should pass 3 fields selections. And its not always 3 fields. Sometimes only 1 field selection, sometimes 2 and sometimes all the 3 field selections might happen. So, I want this to be dynamic which will work for 1 or 2 or 3 field selections.

Thanks

Re: Pass selections to data island table

PiEye — Mon, 05 Jul 2021 16:44:18 GMT

You can either create one variable for each field that you wish to pass into set analysis. The Concat() function takes the currently possible values and doesn't matter if they are selected or not. Just keep adding variables per field.

Sum({<RegionIsland = {$(vRegionSelected)},ProductIsland={$(vProductSelected)}....

If you do not have anything selected it will pass all values - which you may or may not want.

I've just remembered a more concise way of doing this.

you can use "$::[field]" instead

Try

Sum({<RegionIsland = $::Region, ProductIsland = $::Product, YearIsland =$::Year>}Sales)

Re: Pass selections to data island table

Clever_Anjos — Mon, 05 Jul 2021 17:19:09 GMT

Please try this

Sum({<RegionIsland = P(Region), ProductIsland = P(Product), Year = P(Year)>}Sales)

Re: Pass selections to data island table

QlikDev001 — Tue, 06 Jul 2021 00:32:33 GMT

Thanks Clever_Anjos

The problem here is if my Island table has more values than the main table, and if nothing is selected, then it gets the Sales data only for the values available in the main field. If nothing is selected, I want all the values.

Regards

Re: Pass selections to data island table

QlikDev001 — Tue, 06 Jul 2021 00:51:13 GMT

Hey Thanks.

Sum({<RegionIsland = {$(vRegionSelected)},ProductIsland={$(vProductSelected)}...

This works all perfect except that if a Region that is selected in the main table doesn't have value in the data island, it is finally showing 0 even though the Product and Year selected are available. Just one step away 🙂

Regards

Re: Pass selections to data island table

QlikDev001 — Tue, 06 Jul 2021 01:23:00 GMT

Sum({<RegionIsland = {$(vRegionSelected)},ProductIsland={$(vProductSelected)}....

This works perfect. Many thanks

Re: Pass selections to data island table

PiEye — Tue, 06 Jul 2021 07:55:11 GMT

Are you still having an issue with the region selected not being in the other table?