Pass selections to data island table

[QlikDev001]

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

[PiEye]

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?

[QlikDev001]

Hi

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

[PiEye · Accepted solution]

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)

[Clever_Anjos]

Please try this

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

[QlikDev001]

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

[QlikDev001]

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

[QlikDev001]

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

This works perfect. Many thanks

[PiEye]

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