FirstSortedValue in set analysis

Fri, 30 May 2014 17:31:22 GMT

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

topic FirstSortedValue in set analysis in QlikView

FirstSortedValue in set analysis