topic Re: Set Analysis Help (Max) in QlikView

Set Analysis Help (Max)

wonkymeister — Tue, 04 Aug 2015 13:49:03 GMT

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.

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 — Tue, 04 Aug 2015 13:55:05 GMT

Maybe

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

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

wonkymeister — Tue, 04 Aug 2015 14:22:12 GMT

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 — Tue, 04 Aug 2015 14:31:15 GMT

Try this

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

wonkymeister — Tue, 04 Aug 2015 14:50:52 GMT

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

thanks all!!!

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

swuehl — Tue, 04 Aug 2015 15:58:27 GMT

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

wonkymeister — Wed, 05 Aug 2015 11:27:10 GMT

cheers!