Aggr function in Qlik count distinct when sum >0

[Jacob_Weig]

Hello, 

I am trying to do a count distinct of my customers where their "balance" is more than 0.

Here is what I could do on sql:

with table_balance as
(
select customer_id
, sum(case when due_date<CURDATE() then amount else 0 end) as amt_due
, sum(paid_amount) as paid_amt
,sum(case when due_date<CURDATE() then amount else 0 end) - sum(paid_amount) as balance
from customer_loan_payments clp
where active_flag=1
group by 1
having sum(case when due_date<CURDATE() then amount else 0 end) - sum(paid_amount) > 0
)
select count(customer_id) from table_balance

I have tried in qlik:

count(distinct {<active_flag={1}>}
aggr(if((sum({$<[due_date.autoCalendar.Date]={"<$(=Today())"}>} amount)-sum(paid_amount))>0,customer_id),customer_id))

but I get numbers that are way higher in qlik.

Does someone have a clue what I am doing wrong?

[hic]

I would go for an approach without an Aggr(). Try

count( distinct
{<
active_flag= {1},
customer_id= {"=sum({<[due_date.autoCalendar.Date]={"<$(=Today())"}>} amount)-sum(paid_amount))>0"}
>}
customer_id
)

[Jacob_Weig]

Hi Henric, thank you. The code is giving me an error and I am not sure where the bug is. Could you please give me a clue?

Thanks

[hic]

What does the error message look like?

[Jacob_Weig]

it says error in expression, error in set modifier ad hoc element list "," or ")"