Set Analysis Help (Max)

[wonkymeister]

Hi,

some set analysis help please! i'm trying to Sum a field whilst getting the Max value from another (if that makes sense)

e.g.

Quarter	Actual
1	10000
2	10500
3	0

i only want to Sum the Actual for the Max(Quarter) when the Actual > 0

so in the above example i'd be summing the Actual for Quarter 2 = 10,500

make sense?

thanks in advance

Phil.

[swuehl]

Maybe

=FirstSortedValue({<Actual = {">0"}>} Actual, -Quarter)

or

=Sum({<Quarter = {$(=Max({<Quarter = {"=sum(Actual)>0"}>} Quarter))}>} Actual)

[wonkymeister]

afraid not. the first expression returns nothing, the second zero

thanks though!

the second expression looks most likely (not quite sure i fully understand the dollar expansion though - my set analysis is pretty basic!)

[Kushal_Chawda · Accepted solution]

Try this

= sum({<Quarter={"$(=max(if(Sales>0,Quarter)))"}>} Sales)

[wonkymeister]

it Works!!!!!!!!!!!!!!!!!!

thanks all!!!

so its just a case of nesting then? but why the dollar?

[swuehl]

Here is a (short) introduction to set analysis including the use of dollar sign expansion:

A Primer on Set Analysis

The Magic of Dollar Expansions

[wonkymeister]

cheers!