Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld happening right now! You can still join: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Reply
santharubban
Creator III
Creator III

Hi

i didn't understand ur requirement but i have done as per my understanding.PFA