Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

FirstSortedValue in set analysis

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


 

0 Replies