4 Replies Latest reply on Jan 23, 2016 6:08 AM by dan alexandru

# If statement with aggregate and non aggregate items

Hi there!

I've been trying to find a solution to this but no luck.

I need to create a calculated field displaying the SLA% based on last 7 days.

So far I have the following calculations:

Calculated filed for Last 7 Days (values True or False). This is based on the date.

SLA% is an aggregated field (between items in SLA/ total items in SLA buckett)

I've tried if [Last 7 Days]=True then [SLA%] END and also tried LOOKUP(WINDOW_MAX(iif([Last 7 Days],[SLA%],null)),0) but I think I'm not on the correct path. Any ideas? Thanks!

• ###### 1. Re: If statement with aggregate and non aggregate items

Hey Dan,

Instead of doing your if statement on the SLA% field, you will want to move the SLA logic into a new field, prior to the aggregation.

For example:

[SLA Last 7 Days] =

SUM(IF [Last 7 Days] THEN [Between Items in SLA] END)

/

SUM(IF [Last 7 Days] THEN [Total Items in SLA Backet] END)

- Derrick

• ###### 2. Re: If statement with aggregate and non aggregate items

Derrick's reply might take care of it for you, but although you didn't state it in your problem statement, are you getting the error message that you can't mix aggregate and non-aggregates in your calc?

If that's what you are running into, here is how you get around it:

[non-aggregate measure] + [aggregate calc]

do:

ATTR([non-aggregate measure]) + [aggregate calc]

The ATTR function makes the individual measure look like an aggregate to Tableau, and you go on your merry way.

if attr([Last 7 Days]) =True then [SLA%] END

1 of 1 people found this helpful
• ###### 3. Re: If statement with aggregate and non aggregate items

Yes, Joe is right - as long as you have that level of detail on the view that is a quick fix.

Mine is an alternative if you don't.

• ###### 4. Re: If statement with aggregate and non aggregate items

Hey guys, thanks for getting back to me, this was very helpful!

I followed your suggestion Derrick and it worked:

SUM( IF ([Pst Irt Sla Met]='Yes' and [Last 7 Days]=True) THEN 1 END)/

SUM(if(([Pst Irt Sla Met]='Yes' or [Pst Irt Sla Met]='No') and [Last 7 Days]=True) then 1 end) // it works with COUNT too

@Joe, I was getting indeed that error, sorry for incomplete problem. I will use the above for this scenario. Thanks!