Set Analysis for counting customers

[#1]

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

[jagan · Accepted solution]

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.

[#3]

Thanks...it works

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

[jagan]

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.

[#5]

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

[jagan]

Hi,

Try this

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

Hope this helps you.

Regards,

Jagan.

[#7]

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