Show rows that have this AND this but NOT this

[stvegerton]

I want to show Customers that have both Capital AND Aftermarket Usage Qty but NO Service Usage Qty… Maybe with a button? Any advice? Please see the attached.

[pamaxeed]

See my attached sample...

Is that what are you looking for?

Regards,

Patric

[MarcoWedel]

Hi,

maybe like

=Sum({$<[Ship To - Customer #]=P({1<Usage={'Capital'}>}[Ship To - Customer #])*P({1<Usage={'Aftermarket'}>}[Ship To - Customer #])-P({1<Usage={'Service'}>}[Ship To - Customer #])>} Quantity)

hope this helps

regards

Marco

[MK_QSL]

Create a Button

Name whatever you want to give

Go to Action Tab

Add -- Select in Field

Field

[Ship To - Customer Name]

Search String

=Concat({<[Ship To - Customer Name] = P({<Usage = {'Aftermarket'}>})  * P({<Usage = {'Capital'}>}) * E({<Usage = {'Service'}>})  >}DISTINCT [Ship To - Customer Name],', ')

Hope this will help!

[MarcoWedel]

Hi Manish,

nice one.

Just tried to change your expression to get something like

("GENESEE WATER TREATMENT PLANT"|"IDAHO SPRINGS WTP")

as search string and ended up with

='("'&Concat({<[Ship To - Customer Name] = P({<Usage = {'Aftermarket'}>})  * P({<Usage = {'Capital'}>})>}DISTINCT [Ship To - Customer Name],'"|"')&'")'

(leaving out the 'Service' exclusion because the sample data seems to have no matches for the complete condition)

maybe this helps too

regards

Marco

[MK_QSL · Accepted solution]

Yes Marco,

The given sample file is not giving any single Customer due to not fulfilling the requirements.

[stvegerton]

Thank you all so much for the suggestions. I took the weekend off but will give them a try and let you know how it played out!

[stvegerton]

I took what I learned from Manish & Marco and changed the use case ; Show me customers with Capital & Service with NO Aftermarket and it worked! Trying to paste the expression in to share. What is the trick to paste expressions in this forum?

[MK_QSL]

Simple Ctrl + v 

[stvegerton]

That's what I've been trying but it doesn't work. Must be my browser..