Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Limit data with expression

I guess you might need to Suppress those null values

Capture.PNG

8 Replies
galax_allu
Valued Contributor

Re: Limit data with expression

Hi

Try this,

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

MVP
MVP

Re: Limit data with expression

May be like this:

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

chriscammers
Contributor III

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.

simondachstr
Valued Contributor III

Re: Limit data with expression

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

Not applicable

Re: Limit data with expression

Just displays zero?

Not applicable

Re: Limit data with expression

Getting nulls

MVP
MVP

Re: Limit data with expression

I guess you might need to Suppress those null values

Capture.PNG

chriscammers
Contributor III

Re: Limit data with expression

Try this

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