5 Replies Latest reply on Apr 25, 2017 3:22 PM by Justin Larson

# LOD calc... maybe?  I need a way to use MAX or SUM depending on certain situations

I have some data where there are some projects all have a Project ID, but some share a Capital Request (CR).  The data is all in table layout.  What I am trying to do is say IF there are multiple project IDs with the SAME CR then MAX([CR]) should be shown, otherwise SUM([CR]).

I tried IF {FIXED [CR] : COUNT([Project ID])} > 1 THEN MAX([CR]) ELSE SUM([CR]) but it didn't like that.  Is there a way to do an if statement that says if a CR has multiple projects use MAX, but use SUM otherwise?

• ###### 1. Re: LOD calc... maybe?  I need a way to use MAX or SUM depending on certain situations

This should work:

1. Create a calc field: "CR count per PID" with the following formula:

{ FIXED [Capital Request]: COUNT([Project ID])}

2. Create another calc field called "Display CR":

{ FIXED [Project ID]:

if min([CR count per PID])>1 then max([Capital Request]) else sum([Capital Request])

END}

Use Display CR in your view and it will show the max capital request for PIDs having more than 1 CRs else will just show the the sum.

Let me know if this is what you needed!

• ###### 2. Re: LOD calc... maybe?  I need a way to use MAX or SUM depending on certain situations

Seems to give me MAX no matter what.

• ###### 3. Re: LOD calc... maybe?  I need a way to use MAX or SUM depending on certain situations

1) Can you provide a sample dataset and/or workbook or a screenshot of your table/viz?

2) What dimensions are on your view?

2.a) Would you ever run into a situation where a CR is represented in more than one mark on your view, and how would you want the aggregation to work in such a case.

• ###### 4. Re: LOD calc... maybe?  I need a way to use MAX or SUM depending on certain situations

1. Nope, all confidential.

2. Lots, but irrelevant as I have figured out the issue

3. no CR is a Row dimension.  Project ID is the next dimension in the row.

I am pretty sure the issue is the underlying data has duplication in it so it is seeing more than one project ID for each CR no matter what I do.  I'll have to see if I can change the LOD calc to look at unique project IDs.

• ###### 5. Re: LOD calc... maybe?  I need a way to use MAX or SUM depending on certain situations

well, when I say sample data, I don't mean a copy of production data, just a layout that is representative of a few of your rows, since it's hard to know what you mean by "CR is a row dimension".

In any case, sounds  like you've got a solution figured?