Skip to main content
Solved

Count of a specific vendor

K
Budding Pigmenteer
Forum|alt.badge.img+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!

Best answer by Benoit

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

View original

    Benoit
    Community Manager
    Forum|alt.badge.img+13
    • BenoitCommunity Manager
    • Community Manager
    • June 21, 2023

    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
    Budding Pigmenteer
    Forum|alt.badge.img+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
    Forum|alt.badge.img+7

    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
    Community Manager
    Forum|alt.badge.img+13
    • BenoitCommunity Manager
    • Community Manager
    • June 22, 2023

    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
    abhilashsawla
    Benji
    2 people like this
    • abhilashsawla
      abhilashsawla
    • Benji
      Benji

    Reply


    Ask your question here!

    Terms & ConditionsCookie settings

    Cookie policy

    We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

     
    Cookie settings