Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld starts MONDAY! last chance to register is now ! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
porkypig
Contributor II
Contributor II

Exclude values below a threshold number

Hello,

I'm trying to write a set analysis expression that excludes values from a measure below a threshold amount. We're looking at bottom 20 customers over a specific time period and we want to exclude customers to whom we've sold less than 1,040,000 pounds of product. Here's the expression I have so far, I think I'm close but I can't quite get it to work:

num(sum({$<sum([Pounds Sold])={">$(1040000)"}>}[Pounds Sold]),'#,##0;-#,##0')

The number function at the beginning is not absolutely necessary, I can always format it within the table. Any help would be much appreciated.

Labels (3)
1 Solution

Accepted Solutions
rwunderlich

sum({$<[Pounds Sold]={">1040000"}>}[Pounds Sold])

If I misunderstood and you need to sum up the value for the customer before testing, then

sum({$<[Customer]={"=Sum([Pounds Sold]) > 1040000"}>}[Pounds Sold])

You can add the Num function if you like.

-Rob

View solution in original post

4 Replies
rwunderlich

Is there only one [Pounds Sold] value per Customer?

-Rob

porkypig
Contributor II
Contributor II
Author

Hi Rob, yes there is just one [Pounds Sold] value per customer in this instance, it doesn't need to be any more granular than at the customer level

rwunderlich

sum({$<[Pounds Sold]={">1040000"}>}[Pounds Sold])

If I misunderstood and you need to sum up the value for the customer before testing, then

sum({$<[Customer]={"=Sum([Pounds Sold]) > 1040000"}>}[Pounds Sold])

You can add the Num function if you like.

-Rob

View solution in original post

porkypig
Contributor II
Contributor II
Author

Hi Rob, the second expression worked! Thank you!

-Chris