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
Did you mean:
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)

• Set Expression

1 Solution

Accepted Solutions
MVP

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

4 Replies
MVP

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

-Rob

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

MVP

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

Contributor II
Author

Hi Rob, the second expression worked! Thank you!

-Chris