14 Replies Latest reply on Oct 5, 2018 8:18 AM by Joe Oppelt

# All fields must be an aggregate or constant

Hi,

I am having trouble with an aggregate and constant in my calculations. This is what I have so far...

if [index] = 1 then "Include"

ELSEIF lookup([Number of Employees],-1) = LOOKUP([Number of Employees]) then "Do Not Include"

ELSE

"Check formula"

END

• ###### 1. Re: All fields must be an aggregate or constant

Hi Tarang,

Please mark the answer as CORRECT & HELPFUL if it helps you so that it can help others as well

Thanks,

Ritesh

• ###### 2. Re: All fields must be an aggregate or constant

if ([index]) = 1 then "Include"

ELSEIF lookup(ATTR([Number of Employees]),-1) =

(LOOKUP(attr([Number of Employees]))) then "Do Not Include"

ELSE

"Check formula"

END

• ###### 3. Re: All fields must be an aggregate or constant

Hello Tarang,

You can try below approach:

1. previous value = PREVIOUS_VALUE(SUM([Number of Employees]))

2. Include or Do Not Include =

if [index] = 1 then "Include"

ELSEIF [previous value]= sum([Number of Employees]) then "Do Not Include"

ELSE  "Check formula" END

• ###### 4. Re: All fields must be an aggregate or constant

Hi Meenu

This seems to be what I'm looking for. How'd you get Previous Value to display that? All I get is Previous Value = # of Employees.

• ###### 5. Re: All fields must be an aggregate or constant

Hi Tarang,

I have used table calculation as shown below to get the previous value

• ###### 6. Re: All fields must be an aggregate or constant

Hi Meena,

Could you send your version of the file back? Might be easier for me to understand.

• ###### 7. Re: All fields must be an aggregate or constant

Sure,

Attached is in 2018.2.0 version.

• ###### 8. Re: All fields must be an aggregate or constant

Hi Meenu,

I think your solution is super close, but it didn't catch all the use cases. If you see the data below, I'm having an issue with Customer K and the value 17. I've attach a new workbook.

In the sample below Yes should equal Include and No should equal Do not Include.

 Company ID Corporate Name Number of Employees Include? 51071 A 12 Yes 51312 B 12 Yes 51033 C 19 Yes 51374 D 80 Yes 51126 D 500 Yes 50805 E 300 Yes 51207 E 710 Yes 51218 F 25,105 Yes 50709 G 126,000 Yes 51070 H 260,000 Yes 51070 H 260,000 No 51092 I 1 Yes 51093 I 20 Yes 51094 J 19 Yes 51095 J 19 No 50401 K 500 Yes 50306 K 120 Yes 50209 K 30 Yes 50099 K 9 Yes 50088 K 500 No 50077 K 17 Yes 50077 K 17 No 53333 K 14 Yes 53333 K 14 No 50222 K 17 No 50222 K 17 No 56060 K 14 No 54141 K 14 No 51095 J 19 No 52222 J 19 No 51111 L 20 Yes 51111 L 10 Yes
• ###### 9. Re: All fields must be an aggregate or constant

Any idea? Joe Oppelt

Trying to get Yes = Include and No = Do Not Include. Pattern is if the # of employees is repeated within each customer label "Do Not Include"

 Company ID Corporate Name Number of Employees Include? 51071 A 12 Yes 51312 B 12 Yes 51033 C 19 Yes 51374 D 80 Yes 51126 D 500 Yes 50805 E 300 Yes 51207 E 710 Yes 51218 F 25,105 Yes 50709 G 126,000 Yes 51070 H 260,000 Yes 51070 H 260,000 No 51092 I 1 Yes 51093 I 20 Yes 51094 J 19 Yes 51095 J 19 No 50401 K 500 Yes 50306 K 120 Yes 50209 K 30 Yes 50099 K 9 Yes 50088 K 500 No 50077 K 17 Yes 50077 K 17 No 53333 K 14 Yes 53333 K 14 No 50222 K 17 No 50222 K 17 No 56060 K 14 No 54141 K 14 No 51095 J 19 No 52222 J 19 No 51111 L 20 Yes 51111 L 10 Yes
• ###### 10. Re: All fields must be an aggregate or constant

For one thing, when you have two rows of the same Corporate Name and Company ID (such as 50222 and K), you won't get separate rows.  The two 17 values add up to 34.  You need another dimension to differentiate them.

• ###### 11. Re: All fields must be an aggregate or constant

I handled that by making "Number of Employees" into a dimension. That seems to collapse any similar points of data.

• ###### 12. Re: All fields must be an aggregate or constant

Upload a workbook where you did that.

• ###### 13. Re: All fields must be an aggregate or constant

Attached

• ###### 14. Re: All fields must be an aggregate or constant

In the workbook you just uploaded, 50222 shows up once under Corporate Name = K.  In the screen shot where you added me to the thread, 50222 shows up twice there, with the same value of 17.

Do you want two rows of 17 for 50222 there like your screen shot shows?

In the sheet from the workbook I just opened, you have the correct value for [Previous Value] showing up.  (Note:  If you change the table calc on the text shelf to include [Number of employees], you'll get 500 for ID=51374 under Corporate Name = "D".  And ditto for all other multi-ID Corporations.  this will not change the operation of your [Include...] calc.  I'm just pointing out a nuance of table calcs here.)

Given the way you have set up [Previous Value] calc (which does not look at [Number of Employees] in the table calc settings), you could actually change your [Include...] calc to this:

IF not isnull([Previous Value])

//[Previous Value] = attr([Number of Employees])

then "Do Not Include"

ELSE "Include"

END

I commented out your logic and replace it to look at [Previous Value].  When you have a value in [Previous], that's all you need to set to "Do not include".

But the reason your [Include] was not catching the 17 on 50222 was because you have SUM([Number of Employees]) in there.  The SUM for 50222 is 34 because there are two identical rows of 17.  Notice in the part I commented out above that I have changed it to ATTR.  That's what you are using in your [Previous] calc.  That comps to 17, not 34, and [Include] will find that row for 50222 correctly.