Hi,
I got this expression
Sum(Aggr(If(MonthName(Min({1}TxnDate)) = 'May 2013'), Sum({<TxnDate={$(=Date(FirstSortedValue((TxnDate), Aggr(Min({<TxnType={'Invoice'}>}TxnDate),CustomerListID, TxnDate))),'MM/DD/YYYY')}>}Amount), 0), CustomerListID))
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.
If I do not put the set analysis for Txndate then i get sum of 1, 2 3 & 4 because both customers (cust1 and cust2) have their first invoice fallen in this month. Now i need to add a filter or set analysis for dates to eliminate the inclusion of 2 and 4.
Any idea?
This expression
Date(FirstSortedValue((TxnDate), Aggr(Min({<TxnType={'Invoice'}>}TxnDate),CustomerListID, TxnDate))),'MM/DD/YYYY')
, if used individually then it returns right value.
Regards,
Saurabh