Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Concat and Aggr issue to generate a string

Hi all !

I'm trying to generate a specific view for my data. My example data and table are available in attachment.

My data are quite simple, like this :
Year, Product, Salers, Volume
2018, P1, Vendeur1, 47
2018, P2, Vendeur1, 22
2018, P1, Vendeur3, 50
2018, P1, Vendeur3, 15
2019, P1, Vendeur3, 30

I want to display the information in a table like this :

YearResult for P1
2018Vendeur3 (15, 50), Vendeur1 (47)
2019

Vendeur3 (30)

 

For each Year, the result will be the list of salers (with each volume in "()"), order by salers with the smallest sale first. Notice that I had a set analysis for only analyzing Product P1.

In 2018, Vendeur3 appears before Vendeur1 because the smallest sale is 15 for Vender3 vs 47 for Vendeur1. 

It's what i'm expected 😋.
But in my actual reality 😑 :

YearResult for P1
2018Vendeur3 (15, 50), Vendeur1 (47), Vendeur3 ()
2019

Vendeur3 (30)

 

Vendeur3 appears another time with no volume data. In fact, each salers appears like this for each other sales he made. If Vendeur3 have made 5 sales and Vendeur1 : 3 sales, the result will be : 

 

YearResult for P1
2018Vendeur3 (15, 50, 64, 80, 81), Vendeur1 (47, 56, 58), Vendeur3 (), Vendeur1 (), Vendeur1 (), Vendeur3 (), Vendeur3 (), Vendeur3 ()
2019

Vendeur3 (30)

 

My actual expression for this is : 

Concat({<Product={'P1'}>} distinct Salers & ' (' & aggr(distinct Concat({<Product={'P1'}>} Volume, ', '), Year, Salers) & ')', ', ', Volume)

=> I first concat my volume and agregate sales By year and Salers, then concat the agregated result by year order by volume.

Without any success and try many other possibility.

2 concat and 1 aggr is too much 😅.

Perhaps, someone can help me on this ?

Thanks !!

Labels (2)
1 Solution

Accepted Solutions
Highlighted

Re: Concat and Aggr issue to generate a string

Try this

Concat({<Product = {'P1'}>} Aggr(Salers & ' (' & Concat({<Product = {'P1'}>} Volume, ', ') & ')', Year, Salers), ', ', Volume)

View solution in original post

2 Replies
Highlighted

Re: Concat and Aggr issue to generate a string

Try this

Concat({<Product = {'P1'}>} Aggr(Salers & ' (' & Concat({<Product = {'P1'}>} Volume, ', ') & ')', Year, Salers), ', ', Volume)

View solution in original post

Highlighted
Contributor III
Contributor III

Re: Concat and Aggr issue to generate a string

Great, it works !

I see the difference, can't understand completely, but I will try !

Many thanks !