Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set analysis

Hi,

I have a question about set analysis.I started to make a table with the dimention User1 and sum(Count). This works . Now I would like make a second column of sum(Count) of User2. Anybody a suggestion? Your help is very much appreciated!

Kind regards

Set analysis.png

1 Solution

Accepted Solutions
Gysbert_Wassenaar

See attached qvw


talk is cheap, supply exceeds demand

View solution in original post

12 Replies
Gysbert_Wassenaar

See attached qvw.


talk is cheap, supply exceeds demand
swuehl
MVP
MVP

Instead of having two columns User1 and User2, create a single User column:

USER:

LOAD User1 as User,

          1 as Source

RESIDENT YourTable;

LOAD User2 as User,

          2 as Source

RESIDENT YourTable;

Then create a chart with dimension User and two expressions:

=Count({<Source = {1}>} User)

=Count({<Source = {2}>} User)


For the above field transformation, you can also use the CROSSTABLE LOAD prefix.

Not applicable
Author

Hi Gysbert,

pick(match(User1,'Vincent','Dan','David'),

count({<User2={'Vincent'}>} total Count),

count({<User2={'Dan'}>} total Count),

sum({<User2={'David'}>}  Count)

)

Thanx, that works perfect without changing the architecture of my data. However, it's a lot of hard coded names. Do you have a suggestion how to make this thing variable? I have more than 40 names. Thanks in advance!

Not applicable
Author

Thanks, that works perfect. I rather not change my data architecture. However, the expressions are nice and simple like this. Thanks for your time!

qlikviewwizard
Master II
Master II

Hi Maartje,

Please select Correct ,Helpful answers and close the thread.

Thank you.

Gysbert_Wassenaar

You can generate the pick match too using variables:

vUserList: =concat(distinct chr(39) & User1 & chr(39) ,',')

vExpList: =concat(DISTINCT 'count({<User2={' & chr(39) & User1 & chr(39) & '}>}total Count)' ,',', User1)

pick(match(User1, $(vUserList)), $(vExpList) )


talk is cheap, supply exceeds demand
Not applicable
Author

It does not work yet. Do you see what I do wrong? When I look at your variables they look good. The only thing I could think of was that one bracket ')'was forgotten between $(vUserList) and the comma to close the match function. However, my column is still empty. But the function looks the same as the hard coded one, so i'm confused.

community.png

swuehl
MVP
MVP

Could be an issue due to the way you define and expand your variables (i.e. defining with or without leading equal sign), so that the expanded UserList will contain the concat expression, not the strings, comma separated.

See also

The Magic of Variables

Not applicable
Author

Is there another option you suggest?