1 2 Previous Next 16 Replies Latest reply on Oct 12, 2018 10:46 AM by ariya.hemati

# Non Aggregation and Aggregation Error in If Statement

I'm sure this is simple, but I've been running into issues all day.

I want to write a formula where:          If [Transaction Type]="Buy" then [Avg Balance] Else 0 END          Basically, I want to bring the Avg Balance for all my 'buys'.

Transaction Type is a Dimension with the following values:  Null, Buy, Sell   and   Average Balance is calculated field.

Any help would be appreciated!

Thanks

• ###### 1. Re: Non Aggregation and Aggregation Error in If Statement

If  ATTR([Transaction Type])="Buy" then [Avg Balance] Else 0 END

• ###### 2. Re: Non Aggregation and Aggregation Error in If Statement

Hello Ariya,

You can try below logic:

If ATTR([Transaction Type])="Buy" then [Avg Balance] Else 0 END

• ###### 3. Re: Non Aggregation and Aggregation Error in If Statement

Thanks, but I tried it and it did not work.

It removes the error, but doesn't give me the expected value.

If I add the filter, and select 'buy ' from the list, my average balance is different than if i were to use the formula mentioned.

Thoughts?

• ###### 4. Re: Non Aggregation and Aggregation Error in If Statement

Could you please share the logic for "Avg Balance" or workbook?

• ###### 5. Re: Non Aggregation and Aggregation Error in If Statement

Unfortunately I cannot share the workbook, but the logic is:

Avg Balance =

IF [Date Format]="Monthly" THEN SUM([Average Value])

ELSEIF [Date Format]="Quarterly" THEN (SUM([Average Value])/COUNTD([Forecast Date])

ELSEIF [Date Format]="Annually" THEN (SUM([Average Value])/COUNTD([Forecast Date])

END

Average Value is not a calculated measure.  It is a standalone measure.

• ###### 6. Re: Non Aggregation and Aggregation Error in If Statement

IF ATTR([Date Format])="Monthly" THEN SUM([Average Value])

ELSEIF ATTR([Date Format])="Quarterly" THEN (SUM([Average Value])/COUNTD([Forecast Date])

ELSEIF ATTR( [Date Format])="Annually" THEN (SUM([Average Value])/COUNTD([Forecast Date])

END

• ###### 7. Re: Non Aggregation and Aggregation Error in If Statement

IF [Date Format]="Monthly" THEN SUM([Average Value])

ELSEIF [Date Format]="Quarterly" THEN (SUM([Average Value])/COUNTD([Forecast Date])

ELSEIF [Date Format]="Annually" THEN (SUM([Average Value])/COUNTD([Forecast Date])

END

• ###### 8. Re: Non Aggregation and Aggregation Error in If Statement

Yes - the following formula already works..

IF [Date Format]="Monthly" THEN SUM([Average Value])

ELSEIF [Date Format]="Quarterly" THEN (SUM([Average Value])/COUNTD([Forecast Date])

ELSEIF [Date Format]="Annually" THEN (SUM([Average Value])/COUNTD([Forecast Date])

END

• ###### 9. Re: Non Aggregation and Aggregation Error in If Statement

What I'm trying to do now is to have another formula reference the value only for "Transaction Type" = Buy

• ###### 10. Re: Non Aggregation and Aggregation Error in If Statement

If you put Transaction type on Row and Avg Balance on Text label what do you get? show me output or error whatever

• ###### 11. Re: Non Aggregation and Aggregation Error in If Statement

Hi,

You can try :

IF [Date Format]="Monthly"  AND [Transaction Type] = "Buy" THEN SUM([Average Value])

ELSEIF [Date Format]="Quarterly" AND [Transaction Type] = "Buy" THEN (SUM([Average Value])/COUNTD([Forecast Date])

ELSEIF [Date Format]="Annually" AND [Transaction Type] = "Buy" THEN (SUM([Average Value])/COUNTD([Forecast Date])

END

• ###### 12. Re: Non Aggregation and Aggregation Error in If Statement

If i Put Transaction Type on a Row - it works great!

The issue there is that I don't want transaction type to show up in my text table.

• ###### 13. Re: Non Aggregation and Aggregation Error in If Statement

Just uncheck the Show Header Option from the Transcation Type Pill on Rows..Header will go away and also filter it for Buy

• ###### 14. Re: Non Aggregation and Aggregation Error in If Statement

Awesome Deepak! That works!

The one thing i noticed is that my grand totals are incorrect though... thoughts?

1 2 Previous Next