1 Reply Latest reply: Jan 31, 2014 1:29 PM by Santharubban Kumarasamy Shanmugasundaram RSS

    Nr of clients with a balance

      Hi,

       

      I have a problem calculating how many clients we have each month that have a balance with us.

       

      Simplified data is equal to: TransYearMonth, Client, TransAmount

       

      With a date island GraphYearMonth I can get the nr of clients for a selected month like this:

       

      =SUM(IF(AGGR(SUM({<TransYearMonth={"<=$(=ONLY(GraphYearMonth))"}>} TransAmount),Client)>0,1,0))

       

      I can get the balance for each month / each client with this:

       

      =SUM(IF(TransYearMonth<=GraphYearMonth,TransAmount,0))

       

      I get the nr of clients with balance for the first (???) month with this:

       

      =SUM(IF(AGGR(SUM(IF(TransYearMonth<=GraphYearMonth,TransAmount,0)),Client)>0,1,0))

       

      I can hard code one period like this:

       

      =SUM(IF(AGGR(SUM({<TransYearMonth={"<=201304"}>} TransAmount),Client)>0,1,0))

       

      Attached is a simple dummy data example file.

       

      Any ideas on how to solve this?

       

      Kind regards

       

      Niklas