Filter then Count

[shwetagupta]

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.

[sunny_talwar]

May be this

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

[#3]

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.