topic Re: Set analysis with count function in QlikView

Set analysis with count function

Wed, 15 Oct 2014 08:01:07 GMT

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?

Re: Set analysis with count function

tresB — Wed, 15 Oct 2014 08:41:11 GMT

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

Re: Set analysis with count function

jagan — Wed, 15 Oct 2014 08:45:11 GMT

Hi,

Try like this

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

Regards,

Jagan.

Re: Set analysis with count function

mdmukramali — Wed, 15 Oct 2014 08:51:15 GMT

Dear,

Kindly find the attached file.

Thanks

Mukram

Re: Set analysis with count function

simondachstr — Wed, 15 Oct 2014 09:09:03 GMT

An even more slicker expression without using an if statement:

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

Re: Set analysis with count function

Thu, 16 Oct 2014 08:38:07 GMT

Thanks all for the tips

Re: Set analysis with count function

simondachstr — Thu, 16 Oct 2014 08:43:00 GMT

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

This could help other users with a similar issue.

Re: Set analysis with count function

jagan — Thu, 16 Oct 2014 08:43:15 GMT

Hi,

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

Regards,

Jagan.