Help Nested IF statement newbie

[dsarangaya]

Hi Everyone,

i'm having trouble with this one.  i bet this is a piece of cake for most of you.   

Category	Delivery time
Apple	3 days
Orange	12 days
Apple	6 days
Orange	15 days
Apple	2 days
Orange	9 days

I need to show how many late deliveries were there for both categories assuming that a late delivery for category Apple is more than 5 days and category Orange is more than 9 days.  how do i write that in an expression?

[Simon_Astakhov]

If you need to solve it in expression:

count( {<[Category]={"Apple"},[Delivery time]={">5"}>+<[Category]={"Orange"},[Delivery time]={">9"}>} [Delivery time] )

[JordyWegman]

Hi,

Set your Dimension as Category and use the following formula as Measure:

Count(IF((Category = 'Apple' and [Delivery time]>5) OR (Category = 'Orange' and [Delivery time]>9),[Delivery time],null())

// If you really have '5 days' like with text then use this:

Count(IF((Category = 'Apple' and SubField([Delivery time],' ',1)>5) OR (Category = 'Orange' and SubField([Delivery time],' ',1)>9) ,[Delivery time],null()))

Jordy

Climber

[ChiragPradhan · Accepted solution]

Use category as your dimension and the below expression.

Count(if(Category='Apple' and SubField([Delivery Time],' ',1)>5, 1,
		if(Category='Orange' and SubField([Delivery Time],' ',1)>9,1)
        )
     )

[dsarangaya · Accepted solution]

Thank you so much.  This works perfectly.