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

    Nr of clients with a balance



      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:




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




      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