topic Re: Problem in my pivot table in Search the Community

Problem in my pivot table

Mahdi — Wed, 01 Sep 2021 11:56:44 GMT

Hello Everyone,

I have created a pivot table then I dragged and dropped the second attribute in order ro create a matrix, but it doesn't show me all my the rows in my database only if I select a filter as you can see in the screenshots. Please help me resolve this problem.

Thank you for you Help.

Re: Problem in my pivot table

stevejoyce — Wed, 01 Sep 2021 12:28:11 GMT

What is your pivot table measure expression?

Re: Problem in my pivot table

Mahdi — Wed, 01 Sep 2021 12:59:41 GMT

Does my problem come from this ?

Re: Problem in my pivot table

stevejoyce — Wed, 01 Sep 2021 13:31:13 GMT

Maybe your selection is limiting to 1 "nom_niveau_couleur" and without selection there is many values so only() function is returning null. Sorry i don't understand fully with details and language. but that's reasonable to think. What if you do count(nom_niveau_couleur) instead and show as number (or a 2nd expression). Does this equal 1 where you expect it to?

Re: Problem in my pivot table

Mahdi — Wed, 01 Sep 2021 13:39:12 GMT

Yes, It worked but now it doesn't show me the colours .

Re: Problem in my pivot table

stevejoyce — Wed, 01 Sep 2021 15:03:17 GMT

Did you change "Representation" to text? I realized it was going to drop out the colours, I wanted to see what the count number it would return. if it's more then 1 your only function would not work.

Re: Problem in my pivot table

Mahdi — Wed, 01 Sep 2021 15:05:30 GMT

Here is the result

Re: Problem in my pivot table

stevejoyce — Wed, 01 Sep 2021 15:29:49 GMT

So you have cells with > 1 color (seen now with your count(nom_niveau_couleur)). You'll need to determine which color should be displayed in this scenario. For example BETREMIEUX x C#. Maybe its a max date you need to limit color by, or have a numeric colour equivalent and take max colour. Or maybe your colour association doesn't match your pivot table when you thought it does.

But if you use only() and on a measure that has more then 1 value it will return null. I believe this is your issue.

Re: Problem in my pivot table

Mahdi — Thu, 02 Sep 2021 07:35:15 GMT

So how can I get this right so It does show me the highest level or the highest colour ?

Re: Problem in my pivot table

stevejoyce — Thu, 02 Sep 2021 10:31:12 GMT

How do you want to determine "highest level". Let's say you have below where you want the colour_value used to determine which color to take (highest colour_value) in case of a tie

load * inline [

colour, colour_value

red, 10

blue, 9

green, 8

]

;

Try this calculation:

firstsortedvalue([colour], aggr(max(colour_value), [Nom_Complet], [Com_Technologie]))