Trying to use a variable In Set Analysis

[narband2778]

Hello Everyone,

I have created a Variable in the Variables

vCurrentSelection =SubField(GetCurrentSelections(), ':',2)

and now I am trying to use the variable in Set analysis, but it is not working

=Count({<[Session  Name] = {"$(=vCurrentSelection)"}>}[Session  Name])

Can someone help me with this.

thanks in advance.

Naresh

[narband2778]

Hi Sunny,

When I search "XYZ" in Qlik Sense (from Search option on top left) I can see "XYZ" is appearing in 7 different Fields. So, I'll randomly select one field which contains "XYZ" value and now I want to count how many times "XYZ" value is appearing in 7 different Fields.

Thanks,

Naresh

[sunny_talwar]

This can be a little challenging because what you are saying is that you don't already know which field you want to check the value in... it could be in all the fields? to make it little simpler, can you narrow it down to some fields? If not, then we can try to build an expression using Concat() function.

[narband2778]

Let's assume If I need to search "XYZ" in only 5 fields then How can we do this using Concat().

thanks,

Naresh

[sunny_talwar]

Give this a shot

=$(=Concat('Count({1<[' & $Field & ']=p($' & '(=' & Chr(39) & '[' & Chr(39) & Chr(38) & 'SubField(GetCurrentSelections(),' & Chr(39) & ':' & Chr(39) & ', 1)' & Chr(38) & Chr(39) & ']' & Chr(39) & '))>} [' & $Field & '])', '+'))

[sunny_talwar]

This should work for all fields

[narband2778]

mate you nailed it. I can see "XYZ" is appearing 35 times in total. But how can I show the count for each field like this:

Ex: [Field 1]            [Field 2]         [Field 3]

          20                    10                     5

Thanks,

Naresh

[sunny_talwar]

There might not be a dynamic way to do this... but if it is for limited number of fields... you can do this

=Pick(Match($Field, 'Field1', 'Field2', 'Field3', 'Field4'),

Count({1<Field1 = p($(='[' & SubField(GetCurrentSelections(), ':', 1) & ']'))>} Field1),

Count({1<Field2 = p($(='[' & SubField(GetCurrentSelections(), ':', 1) & ']'))>} Field2),

Count({1<Field3 = p($(='[' & SubField(GetCurrentSelections(), ':', 1) & ']'))>} Field3),

Count({1<Field4 = p($(='[' & SubField(GetCurrentSelections(), ':', 1) & ']'))>} Field4))

With $Field as dimension (System field)

[sunny_talwar]

Or Build this pick match using Concat also

[sunny_talwar]

Actually this should work

=$(='Pick(Match($Field,' &Concat(Chr(39) & $Field & Chr(39), ',') & '),' & Concat('Count({1<[' & $Field & ']=p($' & '(=' & Chr(39) & '[' & Chr(39) & Chr(38) & 'SubField(GetCurrentSelections(),' & Chr(39) & ':' & Chr(39) & ', 1)' & Chr(38) & Chr(39) & ']' & Chr(39) & '))>} [' & $Field & '])', ',') & ')')

With $Field as dimension

[narband2778]

Hey Sunny,

Sorry didn't work for me.

What is with $Field as dimension. Do I need to make any changes to your expression when copied. Sorry Bro.