topic Re: Set Analysis for counting customers in QlikView

Set Analysis for counting customers

Tue, 27 May 2014 23:27:19 GMT

Hi,

I need to write a set analysis to count of all customers whose first invoice falls in a particular month.

Can you please help with that?

Regards,

Saurabh

Re: Set Analysis for counting customers

jagan — Wed, 28 May 2014 01:52:24 GMT

Hi Ramya,

Try like this

=Sum(Aggr(If(MonthName(Min(DateFieldName)) = 'May 2014', 1, 0), Customer)

Note : The first invoice is considered as the invoice which is having the lowest date.

Regards,

Jagan.

Re: Set Analysis for counting customers

Wed, 28 May 2014 12:42:22 GMT

Thanks...it works

just one question....may i know your process to reach to this expression..

Re: Set Analysis for counting customers

jagan — Thu, 29 May 2014 01:59:22 GMT

Hi Ramya,

I am getting the Min date of a customer by iterating through the Aggr, and checking whether the minimum date is equal to the given month using if.

Aggr is just like Group by in SQL.

Regards,

Jagan.

Re: Set Analysis for counting customers

Thu, 29 May 2014 16:44:44 GMT

Thanks...

Now I need to do one more task...I need to sum the total amount for those customers who has their first invoices in that month.

It supposed to built upon this expression...

The Amount column is in different table named Txn and there is one to many relationship between Customer and Txn Table...i.e. there could be multiple lines in Txn Table for each customer and we need to sum the amount on all these lines if this customer has its first invoice in this month.

Regards,

Saurabh

Re: Set Analysis for counting customers

jagan — Fri, 30 May 2014 01:51:23 GMT

Hi,

Try this

=Sum(Aggr(If(MonthName(Min(DateFieldName)) = 'May 2014', Sum(Sales), 0), Customer)

Hope this helps you.

Regards,

Jagan.

Re: Re: Set Analysis for counting customers

Fri, 30 May 2014 16:23:51 GMT

Hi Jagan,

It works with a small glitch...

It calculates the sum of sales for all invoices for those customers in that particular month who had their first invoices fallen in that month.

Like if Customer 1 had two invoice on 3/25 and 3/27 then it supposed to take the invoice of only 3/25 and not on of 3/27...

Right now it matches the customer with all transactions in txn table...we perhaps need to put a filter for date so it could choose amount only on the first invoice...i.e. only on 3/25 and not on 3/27

here is something that i tried

=Sum(Aggr(If(MonthName(Min(Date)) = 'May 2014', Sum(Transactions.TxnDate={$(=FirstSortedValue((Transactions.TxnDate), Aggr(Min(TxnDate),CustomerListID, Date)))}>}Sales), 0), CustomerListID))

The expression

FirstSortedValue((Transactions.TxnDate), Aggr(Min({<AccountType={'Income'}>}Transactions.TxnDate),CustomerListID, Date)

gives the correct value when used alone but It is not working when i put this here and the total amount turns out to be zero.

Any Idea?

Here is a data set

It supposed to sum the amount of first invoice of all those customers whose first invoice has fallen in to a particular month.

For Example...

Customer First Invoice Invoice in May 2103 Amount

1. Cust1 3/25/2103 3/25/2013 $25

2. Cust1 3/25/2013 3/27/2013 $125

3. Cust2 3/15/2013 3/15/2103 $100

4. Cust2 3/15/2013 3/18/2013 $120

5. Cust4 1/2/2013 3/28/2013 $100

So the output I need is sum of amount of 1 and 3 i.e. 25 + 100 = $125.

Saurabh