9 Replies Latest reply on May 11, 2017 3:24 PM by Abhinav Kumar

# Aggregate Non-aggregate comparisons

Hi All,

My apologies for the poor heading, cannot think of anything better!

I am facing an issue where I am trying to consider 2 dimensions in a calculated field to calculate a score.

The calculated field(Score) looks like this:

IF [Is Sold]='Yes' AND [Status]='Return' THEN

COUNT([State])*10

ELSEIF [Is Sold]='No' AND [Status]='Return' THEN

COUNT([State])*5

END

And the data set:

 IsSold Status State No Return California Yes Fulfilled California No Fulfilled New York Yes Return New York Yes Fulfilled California Yes Return California Yes Return New York No Return New York No Fulfilled Texas Yes Fulfilled Texas

When I am trying to use the calculated field, it threw an error "cannot convert Aggregate and non-aggregate comparisons or results in 'IF' expressions"

I have attached the workbook for reference.

Any help is appreciated.

Thanks,

Abhi

• ###### 1. Re: Aggregate Non-aggregate comparisons

Hi Abhi,

Your Error is gone but wont return anything as you are multiplying a String with Integar. Check your multiplication, then it will work.

Thanks

Deepak

• ###### 2. Re: Aggregate Non-aggregate comparisons

Hi Abhi,

You just need to put surround [Is Sold] and [Status] with MIN(). Effectively, you want to "aggregate" the two Dimensions so the aggregation (COUNT) on State can occur. Not sure if you want to do a COUNT or COUNTD, so I just followed your original formula.

Walt

1 of 1 people found this helpful
• ###### 3. Re: Aggregate Non-aggregate comparisons

Hi Walt,

Thank you for the quick response. The way you showed will give me the correct response but not in a good visualization.

I want to show only two columns i.e. State and Score.

If I try to show AGG(Score) on Rows shelf it gives me incorrect calculation.

Thanks,

Abhi

• ###### 4. Re: Aggregate Non-aggregate comparisons

Hi Abhi,

My apologies, I didn't understand that was your final goal. I was able to figure out that the issue came from aggregating with the IF function, so I had success breaking the calculation into two calculated fields:

1. Score1

Assign a value of 1 if the Status = 'Return':

2. Score2

If/then statement to multiply Score1 by 10 or 5, depending on the value of [Is Sold]:

3. Add the Measure to the viz and aggregate by SUM:

Let me know if that works for you!

Walt

1 of 1 people found this helpful
• ###### 5. Re: Aggregate Non-aggregate comparisons

Hi Walt,

That was quick! Thank you for that!
This will indeed solve my problem. Just a QQ though, what if I have the following values:

In [Is Sold] column: <Yes, No, Maybe, I don't know, Something else>

In [Status] column: <Return, Not return, Fulfilled, Not fulfilled, Unsold, Sold>

It will be helpful to know because my original data set might have the requirement like this in future!

Thanks again!
Abhi

• ###### 6. Re: Aggregate Non-aggregate comparisons

Hi Abhi,

Glad to help. I'm not quite sure I understand your question. Are you asking if the calculations will be impacted if additional values are present in those two columns? If so, then no, the calculations won't be impacted.

Did I interpret your question correctly?

Walt

• ###### 7. Re: Aggregate Non-aggregate comparisons

Hi Walt,

My apologies for the confusion. Let me draw the table below to make you understand the data set:

 IsSold Status Priority State No Return P1 California Yes Fulfilled P2 California No Fulfilled P3 New York Yes Return P1 New York Yes Fulfilled P2 California Yes Return P3 California Yes Return P1 New York No Return P2 New York No Fulfilled P3 Texas Yes Fulfilled P1 Texas Maybe Unsold P2 Texas I don't know Sold P3 Texas Something else In progress P1 Texas

Currently, I am considering Yes/No from IsSold column and 'Return' from Status column. What if I have to consider all the values for my scoring and a new column called Priority is added, then how do I approach this.

One way I can think of is creating multiple calculated fields for each combination but that is not a feasible case!

I hope I am able to articulate it better now!

Thanks,

Abhi

• ###### 8. Re: Aggregate Non-aggregate comparisons

Hi Abhi,

I now see what you're saying. Regardless of the approach, there will obviously be some sort of manual input required. One approach you could take is to numbers to corresponding values in each field, then create one calculation that will multiply each of these together.

For example, assign values to Priority, Status, and Is Sold:

Then create a score that multiples the three:

It's kind of hard to say on the front end and not knowing the data, but the basic concept applies.

Walt

1 of 1 people found this helpful
• ###### 9. Re: Aggregate Non-aggregate comparisons

Hi Walt,

I agree! Thank you so much for your help!
I really appreciate it!

Best Regards,

Abhi