Combine 2 calculated fields into 1 calculated field in Tableau

Question

I am trying to combine 2 calculated fields into 1 calculated field in tableau.

1st calculated field has this formula:

    SUM(IF [PRODUCT]="MA" THEN((IIF(YEAR([CARRIER_EFFECTIVE_DATE])=2018 AND 
YEAR([CALENDAR_DATE])<=2018, [TOTAL_COMMISSION],0))) END)/SUM(IF [PRODUCT]="MA" 
THEN((IIF(YEAR([CARRIER_EFFECTIVE_DATE])=2018 AND YEAR([CALENDAR_DATE])<=2018, 
[PAID_MEMBERS],0))) END)

2nd calculated field has this formula:

SUM(IF [PRODUCT]="MS"
THEN ((IIF(DATE([CARRIER_EFFECTIVE_DATE])=#2014-01-01# AND DATE([CALENDAR_DATE])
<=#2014-12-31#,[DATE_DEBIT_COMMISSION],0))) END)/ SUM(IF [PRODUCT]="MS"
THEN ((IIF (DATE([CARRIER_EFFECTIVE_DATE])>=#2014-01-01# AND 
DATE([CARRIER_EFFECTIVE_DATE])<=#2014-01-31#,[PAID_MEMBERS],0))) END)

I created these 2 calculations separately but when I am trying to combine together I get an error saying “Cannot mix aggregate and non-aggregate comparisons or results in ‘IF’ expressions" Can someone please help in combining these 2 together?

Can you post your attempt at the combination? Then we can troubleshoot that. — Oso
IF [PRODUCT]="MA" THEN SUM(IF [PRODUCT]="MA" THEN((IIF(YEAR([CARRIER_EFFECTIVE_DATE])=2018 AND YEAR([CALENDAR_DATE])<=2018, [TOTAL_COMMISSION],0))) END)/SUM(IF [PRODUCT]="MA" THEN((IIF(YEAR([CARRIER_EFFECTIVE_DATE])=2018 AND YEAR([CALENDAR_DATE])<=2018, [PAID_MEMBERS],0))) END) ELSE SUM(IF [PRODUCT]="MS" THEN ((IIF(DATE([CARRIER_EFFECTIVE_DATE])=#2014-01-01# AND DATE([CALENDAR_DATE])<=#2014-12-31#,[DATE_DEBIT_COMMISSION],0))) END)/ SUM(IF [PRODUCT]="MS" THEN ((IIF (DATE([CARRIER_EFFECTIVE_DATE])>=#2014-01-01# AND DATE([CARRIER_EFFECTIVE_DATE])<=#2014-01-31#,[PAID_MEMBERS],0))) END) END — user10119684
I tried this but it says "Cannot mix aggregate and non-aggregate comparisons or results in ‘IF’ expressions" — user10119684
SUM(IF [PRODUCT]="MA" THEN SUM(IF [PRODUCT]="MA" THEN((IIF(YEAR([CARRIER_EFFECTIVE_DATE])=2018 AND YEAR([CALENDAR_DATE])<=2018, [TOTAL_COMMISSION],0))) END)/SUM(IF [PRODUCT]="MA" THEN((IIF(YEAR([CARRIER_EFFECTIVE_DATE])=2018 AND YEAR([CALENDAR_DATE])<=2018, [PAID_MEMBERS],0))) END) ELSE SUM(IF [PRODUCT]="MS" THEN ((IIF(DATE([CARRIER_EFFECTIVE_DATE])=#2014-01-01# AND DATE([CALENDAR_DATE])<=#2014-12-31#,[DATE_DEBIT_COMMISSION],0))) END)/ SUM(IF [PRODUCT]="MS" THEN ((IIF (DATE([CARRIER_EFFECTIVE_DATE])>=#2014-01-01# AND DATE([CARRIER_EFFECTIVE_DATE])<=#2014-01-31#,[PAID_MEMBERS],0))) END) END — user10119684

MonteCarloSims MonteCarloSims · Accepted Answer · 2019-02-21T03:53:51

In the combining IF statement, the [Product] field needs to be wrapped in ATTR() to make it an aggregation. Like this...

     IF ATTR([PRODUCT])="MA" ...

My test shows the calculation compiles as valid.

Here is the full calculated field as edited from your original.

IF ATTR([Product])="MA" 
THEN 
SUM(
    IF [Product]="MA" 
    THEN((IIF(YEAR([Carrier Effective Date])=2018 AND YEAR([Calendar Date])<=2018, [Total Commission],0))) 
    END)
/
SUM(
    IF [Product]="MA" 
    THEN((IIF(YEAR([Carrier Effective Date])=2018 AND YEAR([Calendar Date])<=2018, [Paid Members],0))) 
    END) 
ELSE 
SUM(
    IF [Product]="MS" 
    THEN ((IIF(DATE([Carrier Effective Date])=#2014-01-01# AND DATE([Calendar Date])<=#2014-12-31#,
        [Date Debit Commission],0))) 
    END)
/ 
SUM(
    IF [Product]="MS" 
    THEN ((IIF (DATE([Carrier Effective Date])>=#2014-01-01# AND DATE([Carrier Effective Date])<=#2014-01-31#,
        [Paid Members],0))) 
END) 
END

Combine 2 calculated fields into 1 calculated field in Tableau

1 Answers