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
