Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
wonkymeister
Creator III
Creator III

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.

1 Solution

Accepted Solutions
Kushal_Chawda

Try this

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

View solution in original post

6 Replies
swuehl
MVP
MVP

Maybe

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

or

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

wonkymeister
Creator III
Creator III
Author

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

Try this

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

wonkymeister
Creator III
Creator III
Author

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

thanks all!!!

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

swuehl
MVP
MVP

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
Creator III
Creator III
Author

cheers!