1

Total count/sum with greater than condition

We have requirement to get total Purchase order count &  sum of Purchase order amount for only suppliers greater than 2 purchase orders.

Can you please help achieve total count along with having condition.

Sample Input data:-

Category Supplier Name PO Number PO Amount
Food SUP1 PO1 $100
Service SUP2 PO2 $200
Food SUP2 PO3 $100
Merchandise SUP3 PO4 $600
Service SUP4 PO5 $50
Food SUP1 PO6 $100
Food SUP1 PO7 $100
Service SUP2 PO8 $200
Food SUP2 PO9 $100
Merchandise SUP5 PO10 $600
Service SUP1 PO11 $50
Food SUP5 PO12 $100

 

sample output:-

Category Total Suppliers Total PO's Total PO Amount
Food 3 6 $600
Service 2 3 $450
Merchandise 1 1 $600
7replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi Mehraj,

    Can you try using Aggregate Filter to filter Purchase Order count  greater than 2?

    Screen shot below shows how to apply this filter.

    Like
  • Hi Anurag,

     

    Thanks for prompt response. I tried aggregate filter but it does not affect the result shown.   As the po# is a string with transaction number. In the screenshot you shared did it show data correctly?

     

    Thanks

    Mehraj

    Like
  • Hi Mehraj,

    It's ok if  PO # is a string with transaction number.

    it should work. In your screen shot i don't see filter getting applied. No value is selected from list where you see 1, 2, 10, 12.  

    Like
      • Mehraj
      • Starbucks
      • burgundy_sail
      • 1 mth ago
      • Reported - view

      Anurag Malik 

      Apologies may be screenshot was not showing the value but it is selected. Attaching  word file with screenshots and a short recording to compare with & without aggregate filter results.

      Like
  • Hi Mehraj,

    My bad. This will not work. In dimension you have Sourcing_Group and aggregate filter is getting applied for dim Sourcing_Group.

    If supplier was used as dimension then this would have worked.

     

    Let me share a different solution for this.

    Like
  • Hi Mehraj,

    This video may help in designing the solution:

    https://youtu.be/MVHprH044Mc

    Like 1
  • Hi Anurag,

    Thank you so much for detailed video. Solution works perfectly for our requirement. Really appreciate showing how to add dynamic variable.

    Have a great day!

    Thanks

    Mehraj

    Like
Like1 Follow
  • 1 Likes
  • 1 mth agoLast active
  • 7Replies
  • 31Views
  • 2 Following

Product Announcement

Incorta 4.9 is now Generally Available (GA)!!!