Solved

Using IF and AND function together in a formula?

  • 15 February 2024
  • 2 replies
  • 132 views
Cat M
Badge +1
  • Cat M
  • Occasional Observer
  • 3 replies

Hi, 

I want to use the AND function together with the IF function in one formula in a property of a transaction list. Is this possible in Pigment? I can’t seem to get the AND part to be recognised when using it as part of the logical test of an IF statement, the way you can in Excel.

 

In Excel for example I can write an IF statement using AND:
=IF(X2>7,"check",IF(AND(E2="Direct",M2="Retail O&O",X2>0),"check","OK"))

 

But in Pigment when I type AND as part of my IF statement, the function doesn’t seem to be recognised and my formula doesn’t work. The text for ‘AND’ doesn’t show up in green the way it does for the IF function (or other functions.)

 

I don’t have any issues using the IF function otherwise, or nesting multiple IFs in a formula, it’s just that I can't seem to use IF with AND. Please let me know if it’s possible, or if perhaps I need to write it in a different way? If not possible I suppose I can create a separate property to do the AND check.

 

Thanks,

Cat

icon

Best answer by francois 15 February 2024, 16:55

View original

2 replies

francois
Rank
Userlevel 6
Badge +14

Hi Cat,

AND is not used the same way in Pigment as in Excel, where it is a function - but as an operator, like so:

IF(Country = Country."France" AND Revenue > 1000, "Wow this is crazy good!")

Can you share your formula in a Formula Playground, and maybe describe in plain words what you’re trying to achieve?

François (Pigment)
Cat M
Badge +1

Hi François,

Thanks for the info, it’s clear to me now. I have been able to add AND as an operator in the formula with IF function. (See below first part of the formula I tested - seems to be working for me. Still need to add some more nested IF functions, but I know how to use AND now 😀)

We have a transaction list with a list of orders currently processing at our DC (updating daily), and I want to be able to to flag the ones that are processing for too long without being shipped and see the overall quantity of these. I am using a formula in a property to calculate an ‘Order aging’ dimension to flag the orders that are not shipping quickly enough, and then I will make some metrics based on this to both view monthly trends in a board and provide a list of older orders to flag. The formula needs to use both IF and AND as for different order types there are different criteria for what we would consider problematic aging. For anything older than 7 working days it needs to be flagged, but then on top of that for certain customers/order type combinations it needs to be flagged already if it is older than 2 days, while for another we need to flag it if it didn’t ship same day, and so on.

Below first part of the formula since you asked, but I think I’m on the right track now and will be able to add the rest of the nested IF functions and then create the metrics without any issues.

IF('SO Sequence'.'NETWORKDAYS DN Creation'>7,'SO DN Aging Check'.Name."Check",IF('SO Sequence'.'Document Type Cluster'='Document Type Cluster'.Name."Direct" AND 'SO Sequence'.'Sales Channel'='Sales Channel'.Name."Retail O&O" AND 'SO Sequence'.'Season Year'='Season Year'.Name."9999" AND 'SO Sequence'.'WEEKDAY SO Creation'=2 AND 'SO Sequence'.'Created By'='SO Created By'.Name."RFC_PCP300" AND 'SO Sequence'.'NETWORKDAYS DN Creation'>0,'SO DN Aging Check'.Name."Check",'SO DN Aging Check'.Name."OK"))

Thanks again,

Cat

Reply


Ask your question here!

Terms & ConditionsCookie settings