7 Replies Latest reply: Jun 18, 2018 5:50 AM by Rangam Seshadri RSS

    Count of selection in expression, unique value

    Ugur Arslan

      Dear all,

       

      I have a aging balance (outstanding amounts) which looks like the following.

       

      Seller IDCar TypeOn HoldCurrentDue 1-30Due 31-60Due 61-90Due 91-120Due 120+
      1456OpalYES $12.00 $-   $345.00 $-   $75.00 $-  
      3478FerdNO $45.00 $-   $566.00 $664.00 $-   $-  
      478382PirscheNO $-   $788.00 $7.00 $6,443.00 $-   $97.00
      48392874OpalNO $-   $9.00 $8.00 $67.00 $7.00 $-  
      247487OpalYES $87.00 $-   $99.00 $8.00 $-   $-  
      47465HundaNO $789.00 $346.00 $6.00 $8.00 $7.00 $-  
      3928094PirscheYES $80.00 $9.00 $54.00 $99.00 $-   $9.00
      278479874OpalNO $-   $8.00 $6.00 $7.00 $89.00 $56.00

       

      The question which I want to answer is:

       

      How many Seller ID from the Car Type: Opal have an overdue balance which is over 90 days due.

       

      The result which I expect to see is 2 (please see below which seller ID. ) I want to see the total of the unique seller id's.

       

      Seller IDCar TypeOn HoldCurrentDue 1-30Due 31-60Due 61-90Due 91-120Due 120+
      48392874OpalNO $-   $9.00 $8.00 $67.00 $7.00 $-  
      278479874OpalNO $-   $8.00 $6.00 $7.00 $89.00 $56.00

       

      The expression I write:

       

      Count({<[Car Type]={'Opal'},[On Hold]={'No'},[AR Aging Bucket]={'Due 91-120'}+{'Due 120+'}>}[AMT USD]) doesn't give me the right answer.

       

      Can you please help me what the right expression should be?

       

      Thanks

       

      Ugur