Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
cdecluseau
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 (1)
1 Solution

Accepted Solutions
sunny_talwar

Try this

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

View solution in original post

2 Replies
sunny_talwar

Try this

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

View solution in original post

cdecluseau
Contributor III
Contributor III
Author

Great, it works !

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

Many thanks !