Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Displaying a matrix based on distinct count

Hello community,

 

I have two tables one called SPEND and the other one called CATALOG

 

In the SPEND table i have Total Spend, Supplier Code, Supplier Name, Document Number , Group Mechandise

In the CATALOG table i have Class, Domain, Family, Group Merchandise

 

The relation between the two table is * to 1.

 

I want to display the total sales per domain but when supplier has just one document number.

This result:

When filtering the table based on distinctcount =1 of Document Number it shows no result 

 

When just putting all the value in matrice it shows me the total documents like that 

 

I can't find a solution for this, could you help me please

 

Thanks

 

 

 

 

Need Help

2 Replies

  • Magpie_Rob's avatar
    Magpie_Rob
    Icon for Helper I rankHelper I
    5 years ago

    Hi Anonymous 

     

    can you add a calculated column and filter on it? something like below then add a visual filter for the 1

    onedoc = IF(DISITNCTCOUNT ( Document Number ) = 1 , 1, 0 )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      thank Magpie_Rob but it doesn't work because the distinctcount should be applied on the supplier, so I want to know which supplier had only one document then this number should be displayed with total spend per domain