topic Re: Moving multiple dimension values to one expression in QlikView

Moving multiple dimension values to one expression

zagzebski — Sat, 14 May 2016 12:47:26 GMT

I need to convert the "Current" chart below to "What I want" chart. Basically rather than have a sales dimension with three (3) people I want to put the names of those three people into one expression. Any ideas?

Current:
Customers	Salesperson	Region A	Region B	Region C
SZ Windows	Steve	150	0	0
DK Instruments	Derek	0	200	0
BD Siding	Brian	0	0	300
What I want:
Customers	Region A	Region B	Region C	Salespersons
SZ Windows	150	200	300	Steve, Derek, Brian

Thanks,

Zag

Re: Moving multiple dimension values to one expression

swuehl — Sat, 14 May 2016 13:09:12 GMT

Using Customers as dimension and

=Concat(DISTINCT Salespersons,', ')

as expression?

But it seems like sales persons are not having same Customer, so I am not sure how you want to get these into one line

Maybe like

=Concat(TOTAL DISTINCT Salespersons,', ')

Re: Moving multiple dimension values to one expression

zagzebski — Sat, 14 May 2016 15:05:13 GMT

Thanks. This worked. Really appreciate it.

Zag

Re: Moving multiple dimension values to one expression

zagzebski — Sat, 14 May 2016 20:05:37 GMT

One quick follow up. Is there any way to change the concatenation so if I filter on one of the salesperson's it will search for that person's name anywhere within the string.

For instance if the expression:

=Concat(DISTINCT Salespersons,', ') = "Steve, Derek, Brian" and I filter on Steve it will show, but if I filter on Lisa this line will not show because Lisa is not in the string.

Zag

Re: Moving multiple dimension values to one expression

swuehl — Sat, 14 May 2016 21:32:31 GMT

Maybe like attached, using an expression in the chart like

=If(Only(Cat),Concat({<SalesPerson>} DISTINCT SalesPerson, ','))

Re: Moving multiple dimension values to one expression

zagzebski — Sat, 14 May 2016 22:51:18 GMT

Your example gives me exactly what I want but unfortunately I am getting a null ("-") value in my application. I will see if I can find the difference.

Zag