6 Replies Latest reply on Nov 26, 2018 5:49 PM by swaroop.gantela

# Dividing percentages by percentages in IF statement

Hi,

I have a calculated field called PA Denial % which uses the following formula:

SUM([PA Denials])/SUM([Pa Requests])

I now wish to calculate the QoQ figure between Q3 and Q2 2018 but I am running into issues in my new calculated field which should calculate this.

My QoQ PA Denial % calculated field uses the following code:

(sum(if datepart('quarter',[Date Parse Quarter Month]) = 3 then [PA Denial %] end) -

sum(if datepart('quarter',[Date Parse Quarter Month]) = 2 then [PA Denial %] end))

/

sum(if datepart('quarter',[Date Parse Quarter Month]) = 2 then [PA Denial %] end)

I am getting errors about mixing aggregate and non-aggregate comparisons/results in an IF statement, can anyone give me a steer how to fix this? • ###### 1. Re: Dividing percentages by percentages in IF statement

John,

Just enclosed your quarter dim with ATTR. like:

attr([date parse quarter month])

Thanks,

AB

1 of 1 people found this helpful
• ###### 2. Re: Dividing percentages by percentages in IF statement

Hi Ankit,

I've wrapped the date field with ATTR but I am still getting the same error:

(sum(if datepart('quarter',attr([Date Parse Quarter Month])) = 3 then [PA Denials] end) -

sum(if datepart('quarter',attr([Date Parse Quarter Month])) = 2 then [PA Denials] end))

/

sum(if datepart('quarter',attr([Date Parse Quarter Month])) = 2 then [PA Denials] end)

• ###### 3. Re: Dividing percentages by percentages in IF statement

Hi John,

Your PA Denial % field is already an aggregate.  You're trying to aggregate it again by wrapping the whole if statement in sum() but Tableau won't let you aggregate a field that's already an aggregate.

I think this will work for you:

((if datepart('quarter',[Date Parse Quarter Month]) = 3 then [PA Denial %] end) -

(if datepart('quarter',[Date Parse Quarter Month]) = 2 then [PA Denial %] end))

/

(if datepart('quarter',[Date Parse Quarter Month]) = 2 then [PA Denial %] end)

Best,

Paul

• ###### 4. Re: Dividing percentages by percentages in IF statement

Hi Paul,

Thanks for the reply but unfortunately I am still having issues with mixing aggregate and non-aggregate data after using your recommendations above. Is there any merit in using @Ankit's approach of ATTR for my date field?

• ###### 5. Re: Dividing percentages by percentages in IF statement

Good point. Yea wrap the dates in attr() again but leave the sums out of it like I had it. I think that'll do it.

• ###### 6. Re: Dividing percentages by percentages in IF statement

John,

Even with those issues resolved, there may be some other factors to consider.

I think the SUM calculation is carried out on a row level, and so the quarter 3

PA Denial % will not have access to the quarter 2 value which is on a different row.

I think may need to use a window calculation along the lines of

([PA Denial %]-LOOKUP([PA Denial %],-1))

/

LOOKUP([PA Denial %],-1)

Please see workbook v10.3 and sample datasource attached in the Forum Thread.

Dividing percentages by percentages in IF statement

If either is not matching in form what you are seeking to achieve, would be

grateful if you would adjust them and re-post.