Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

wonkymeister
Not applicable

Set Analysis Help (Max)

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.

QuarterActual
110000
210500
30

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.

Tags (1)
1 Solution

Accepted Solutions
kush141087
Not applicable

Re: Set Analysis Help (Max)

Try this

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

6 Replies
swuehl
Not applicable

Re: Set Analysis Help (Max)

Maybe

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

or

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

wonkymeister
Not applicable

Re: Set Analysis Help (Max)

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!)

kush141087
Not applicable

Re: Set Analysis Help (Max)

Try this

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

wonkymeister
Not applicable

Re: Set Analysis Help (Max)

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

thanks all!!!

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

swuehl
Not applicable

Re: Set Analysis Help (Max)

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
Not applicable

Re: Set Analysis Help (Max)

cheers!