Set analysis with count function

[#1]

Hi,

I have a sales table and want to count the no. of customer that only have one transaction in the year.

Tx Date     Invoice No.     Customer ID     Sales Amount

Jan 2013     A0000          CS0001         

Jan 2013     A0001          CS0002

Jan 2013     A0002          CS0001

Feb 2013     A0003          CS0001

Jan 2014     A0004          CS0003

Jan 2014     A0005          CS0003

Feb 2014     A0006         CS0002

My expected result will be

Year     No. of one time purchase customer

2013     1

2014     1

Can I use a count function in a set analysis to filter those Customer ID that has multiple sales?

[tresesco]

If you are thinking of using a chart, set analysis might not be the solution. Try:

Straight table:

Dim: Year

Exp1: FirstsortedValue(CustomerID, Aggr(Count(CustomerID),Year,CustomerID))

Exp2:Count(If(Aggr(Count(CustomerID), Year, CustomerID)=1, CustomerID))

Edit: Corrected

[jagan]

Hi,

Try like this

=Count(Aggr(If(Count(CustomerID) =1, 1), Year, CustomerID))

Regards,

Jagan.

[mdmukramali]

Dear,

Kindly find the attached file.

Thanks

Mukram

[simondachstr · Accepted solution]

An even more slicker expression without using an if statement:

=sum(-aggr(Count([Customer ID])=1,[Customer ID],Year))

[#6]

Thanks all for the tips

[simondachstr]

Can you mark the one that helped your case as the correct one please.

This could help other users with a similar issue.

[jagan]

Hi,

Close this thread if you got the answer by giving Correct and Helpful answers to the useful posts.

Regards,

Jagan.