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: 
shwetagupta
Partner - Creator II
Partner - Creator II

Filter then Count

Hi All,

Need help over constructing a formula.

I need to figure out 'REPEAT' customers.

For customer to be repeat following are the conditions

(I) Drop all the cancelled order

(ii) After dropping all the cancelled, Count(Invoice_ID)>3

Calculate Count of Customer!

Thats what I have tried using: Sum(Aggr(If(status<>'CANCELLED' and Count(distinct Invoice_ID)>3,1), Contact_ID))

I need to place a condition which can first filter the Non cancelled order out which i should check customer having more than 3 invoices.

2 Replies
sunny_talwar

May be this

Count(DISTINCT {<Contact_ID = {"=Count({<Status -= {'CANCELLED'}>}Invoice_ID) > 3"}>} Contact_ID)

Not applicable

Hi Shweta,

Try using a nested if condition.

Create a variable that does the count(distinct Invoice_ID)

vcount = count(distinct Invoice_ID)

then in the measure :

sum(if(status<>'CANCELLED',if(vcount > 3,1)))

Similarly if you are doing any nested aggregation that should also work fine using a variable I guess.