8 Replies Latest reply: Jul 21, 2016 12:06 PM by Chris Cammers

# Limit data with expression

I only need to see counts of 3 or more?  Can I do this in the sheet expression?

Expression

Count([DayName(Submitted)]) and ([DayName(Submitted)]) >= '3')

• ###### Re: Limit data with expression

Hi

Try this,

Rank(count([DayName(Submitted)]))>=3

• ###### Re: Limit data with expression

Just displays zero?

• ###### Re: Limit data with expression

May be like this:

If(Count([DayName(Submitted)]) > 3, Count([DayName(Submitted)]))

• ###### Re: Limit data with expression

Come on Set Analysis expert - I am sure you can do better than an if-statement

• ###### Re: Limit data with expression

Getting nulls

• ###### Re: Limit data with expression

I guess you might need to Suppress those null values

• ###### Re: Limit data with expression

Try this

Count({<RxNo = {"=Count([DayName(Submitted)])>3"}>}[DayName(Submitted)])

• ###### Re: Limit data with expression

For this type of situation I like to use Set Analysis. It will perform better than using If or ranking.

The method does have some limitations but this seems like a case where it would work.

You need to decide the appropriate dimension to filter against.

For example you might want to get customers who have more than 100,000 on deposit, or orders greater than a certain amount.

For the example I am attaching I want to see customers who have purchased 1000 items or more.

My expression looks like this...

Sum({<Customer = {"=Sum(ItemsPurchased)>=1000"}>}ItemsPurchased)

in set analysis the double quotes make the arguement a Search String which you can enter formulas like =Sum(ItemsPurchased)>=1000. Qlik will calculate the expression for each value in Customer and evaluate if the customer is part of the result set.

The main limitation is that you can only limit based on one dimension field, in our case customer. If you wanted to limit by month too you'd have to use a different approach.