0 Replies Latest reply: Jun 29, 2018 4:12 AM by Ugur Arslan RSS

    Sum Total AR

    Ugur Arslan

      Dear all,

       

      I have a table with the following aging

       

      Seller IDSuspend IndicatorCurrent1-30 days31-60 days61-90 days91-120 days121-150 days150-181 days181+ days
      1Suspended$5.00 $67.00 $67.00 $-   $7.00 $74.00 $734.00 $-  
      4Not Suspended$66.00 $7.00 $-   $7.00 $8.00 $-   $-   $73.00
      7Suspended$7.00 $78.00 $8.00 $-   $-   $-   $-   $-  
      90Not Suspended$-   $-   $-   $-   $-   $-   $-   $-  
      660Not Suspended$7.00 $-   $6.00 $3.00 $3.00 $67.00 $7.00 $-  
      3456Suspended$-   $8.00 $-   $-   $-   $4.00 $3.00 $7.00
      5677Not Suspended$7.00 $8.00 $7.00 $-   $-   $-   $-   $-  
      89877Suspended$8.00 $-   $-   $7.00 $7.00 $-   $-   $3.00
      321234Not Suspended$-   $-   $-   $-   $-   $-   $-   $-  
      445444Suspended$888.00 $-   $-   $8.00 $8.00 $-   $3.00 $-  
      456778Not Suspended$-   $-   $5.00 $-   $-   $7.00 $7.00 $-  
      7883983Suspended$87.00 $-   $-   $45.00 $8.00 $-   $3.00 $7.00
      8498989Not Suspended$-   $85.00 $-   $-   $-   $-   $-   $-  
      44929029Suspended$54.00 $-   $5.00 $-   $4.00 $-   $-   $7.00

       

      In case I want to see the count of seller ID's who have an balance above 90 days and are suspended I use the following expression:

       

      count({<[[Suspend Indicator]={'Suspended'},[AR Aging Bucket]={'91 - 120 days','121 - 150 days','151 - 180 days','181+ days'},[Seller ID]={"=sum([AR Amt USD])>0"}>}distinct [Seller ID])

       

      Result is 6 seller ID's who are suspended and have a balance + 90 days.

       

      Seller IDSuspend IndicatorCurrent1-30 days31-60 days61-90 days91-120 days121-150 days150-181 days181+ days
      1Suspended$5.00 $67.00 $67.00 $-   $7.00 $74.00 $734.00 $-  
      3456Suspended$-   $8.00 $-   $-   $-   $4.00 $3.00 $7.00
      89877Suspended$8.00 $-   $-   $7.00 $7.00 $-   $-   $3.00
      445444Suspended$888.00 $-   $-   $8.00 $8.00 $-   $3.00 $-  
      7883983Suspended$87.00 $-   $-   $45.00 $8.00 $-   $3.00 $7.00
      44929029Suspended$54.00 $-   $5.00 $-   $4.00 $-   $-   $7.00

       

      I have the following questions.

       

      Now I want to get the total balance for only these customers. Result Should be $ 2.128,- (please see orange marked in below table).

       

      Seller IDSuspend IndicatorCurrent1-30 days31-60 days61-90 days91-120 days121-150 days150-181 days181+ days
      1Suspended$5.00 $67.00 $67.00 $-   $7.00 $74.00 $734.00 $-  
      4Not Suspended$66.00 $7.00 $-   $7.00 $8.00 $-   $-   $73.00
      7Suspended$7.00 $78.00 $8.00 $-   $-   $-   $-   $-  
      90Not Suspended$-   $-   $-   $-   $-   $-   $-   $-  
      660Not Suspended$7.00 $-   $6.00 $3.00 $3.00 $67.00 $7.00 $-  
      3456Suspended$-   $8.00 $-   $-   $-   $4.00 $3.00 $7.00
      5677Not Suspended$7.00 $8.00 $7.00 $-   $-   $-   $-   $-  
      89877Suspended$8.00 $-   $-   $7.00 $7.00 $-   $-   $3.00
      321234Not Suspended$-   $-   $-   $-   $-   $-   $-   $-  
      445444Suspended$888.00 $-   $-   $8.00 $8.00 $-   $3.00 $-  
      456778Not Suspended$-   $-   $5.00 $-   $-   $7.00 $7.00 $-  
      7883983Suspended$87.00 $-   $-   $45.00 $8.00 $-   $3.00 $7.00
      8498989Not Suspended$-   $85.00 $-   $-   $-   $-   $-   $-  
      44929029Suspended$54.00 $-   $5.00 $-   $4.00 $-   $-   $7.00

       

      Can you please help me?

       

      Thanks

       

      Ugur