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 :
| Year | Result for P1 |
| 2018 | Vendeur3 (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 😑 :
| Year | Result for P1 |
| 2018 | Vendeur3 (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 :
| Year | Result for P1 |
| 2018 | Vendeur3 (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 !!
