Solved

Count of a specific vendor

  • 21 June 2023
  • 4 replies
  • 94 views
K
Rank
Badge +1

Hi!


I’m trying to count all the “No Vendor Name” entries from my transaction list but keep getting this error that the Property is not unique. Final Vendor Name property in the transaction list cannot be unique since there are multiple transactions under the same vendor.

IF('PL GL Data'.'PL Cat 1'='PL Cat 1'."Non-Headcount Costs",COUNTALLOF('PL GL Data'.'Final Vendor Name'."No Vendor Name"))


 


Can anyone please suggest any modifications in my current formula or any other way to count this specific “No Vendor Name” from the transaction list.

Thank you!

icon

Best answer by Benoit 22 June 2023, 09:29

View original

4 replies

Benoit
Rank
Userlevel 6
Badge +12

Hi, 

I believe you can use the same logic as the first part of your formula:
List.’Dimension Property’ = Dimension.”item”

Because “No Vendor Name” is found multiple times in ‘PL GL Data.’Final Vendor Name’, you have to reference the dimension itself.

That would give something like:

IF('PL GL Data'.'PL Cat 1'='PL Cat 1'."Non-Headcount Costs",COUNTALLOF('PL GL Data'.'Final Vendor Name' = ‘Final Vendor Name’."No Vendor Name"))

assuming your dimension is named ‘Final Vendor Name’, otherwise, write the dimension block name instead.

 

Hope it helps.

Best,
Benoit

 

Benoit - Pigment wizard
K
Rank
Badge +1

Hi Benoit,
Thank for your suggestion. The property is resolved now.

But I’m not getting the correct COUNT of “No Vendor Name” for this formula. The count that I get from manually applying filters in the transaction list is different from this formula.

Am I missing anything else to add in here to arrive at the correct count?

 

abhilashsawla
Userlevel 4
Badge +6

Hi @keerthanaramesh 
 

Please try Below formula:
 

IF('PL GL Data'.'PL Cat 1'='PL Cat 1'."Non-Headcount Costs", if('PL GL Data'.'Final Vendor Name' =‘Final Vendor Name’."No Vendor Name",1,0)[Remove:'PL GL Data'])

 

Benoit
Rank
Userlevel 6
Badge +12

Hi, 

I recreated a List as follow

 

If you want the count, you’re not even forced to do an IF, you can just filter your list to have the count of the desired items, which would be more performant.

 

Here is the formula:

'PL GL Data'.Name

[filter: 'PL GL Data'.'PL Cat 1' = 'PL Cat 1'."Non-Headcount Costs"]

[filter: 'PL GL Data'.'Final Vendor Name' = 'Final Vendor Name'."No Vendor Name"]

[REMOVE count: 'PL GL Data']

 

Hope it helps.

Thanks

Benoit - Pigment wizard

Reply


Ask your question here!

Terms & ConditionsCookie settings