7 Replies Latest reply on Sep 13, 2017 8:25 AM by Wes Patton

Conditional Weighted Average

I wanted to calculate a weighted average, for example WA Current FICO Score, but only do it if the score is valid.

If I segment the numerator and denominator, I can get it to work fine, but there has to be a way to do it in one calculation, right?

N = if ([Curr FICO])>300 then ([Curr FICO]*[Src Outstandings]) END

D = if ([Curr FICO])>300 then ([Src Outstandings]) END

WA = N/D

I really just want if Curr FICO > 300 then sum(FICO * OS) / sum(OS) in one place.

TIA

• 1. Re: Conditional Weighted Average

Hi Wes

If you cold add packaged workbook (**.twbx) , it's really helpful/easy for us to start investigating.

Thanks.

Shin

• 2. Re: Conditional Weighted Average

Hi Wes

What was the problem when your tried the last formula      if Curr FICO > 300 then sum([Curr FICO] * Src Outstandings]) / sum(Src Outstandings]) End

were you not getting an answer or were you getting the wrong answer and are the data coming from the same source, are there Nulls in the data that need to be accounted for with zn() funcitons or ifnull statements?  On the surface it looks like the formula should have worked

Jim

Let me know if this helped

• 3. Re: Conditional Weighted Average

That gives me the aggregate/non-aggregate mixing error.

• 4. Re: Conditional Weighted Average

Thanks now that is something we can workd with

ALL of the data fields need to be aggregated  as in

if         Attr(Curr FICO) > 300 then sum([Curr FICO] * [(Src Outstandings]) / sum([Src Outstandings]) End

Attr(Curr FICO) > 300 - you can either use attr(), Max(), or Min() depending on your data and what makes sense in your if test''

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• 5. Re: Conditional Weighted Average

Yeah, that's where it stops working.  I've attached an example book.  WA_Calc doesn't return anything, despite looking just like the manual calculation.

• 6. Re: Conditional Weighted Average

Hi

Interesting your own manual solutions is the corect one - I just put the formulas for the Numerator and denominator into a single formula and it illustrates the issue

sum(if [Discount] > 0 then [Discount]*[Sales] END) /sum(if [Discount] > 0 then [Sales] END)

Each part is only true when the discount is % 0 and it is only after the test that you perform the aggregation -

you want the numerator TOTAL divided by the denominator TOTAL - and not the total after the division has been made

Jim

1 of 1 people found this helpful
• 7. Re: Conditional Weighted Average

Yeah, Tableau is parsing it strangely, compared to other languages, I think.  Thanks again.