Aggr spend on Accounts and count Accounts with <>0

[Anonymous]

Hi All,

Im sitting with two formulars in Qlik sense which has been giving me some problems for a few hours now. It would be greatly appreciated if one of you could give me some input to a solution.

Problem 1:

I have several contracts with debit/credit on them belonging to different categories, each contract can only belong to one category, however a catogory can have several contracts (1 to many) e.g.

Category          Contract               Debit/credit

A                              X1                    100

B                              X2                    200

C                              X3                    300

D                              X4                    -200

D                              X4                    200

C                              X3                    -200

Total                                                 

What i want to do is to calculate the number of contracts that have a spend with <> than 0 so my contracts where the Debit/credit sum for an contract is zero is excluded.(above example would give 3 total and 1 on A, 1 on B and 1 on C

My initial idea was the following formular : count(distinct if ( aggr ([Debit/credit]] , [Contract] ) <> 0, [Contract]))

Problem 2:

This is essentially the same as the above however i would like to only count the contracts that account for top 90% of the Total Debit/credit

Thanks in advance ! 

[vishsaggi]

Try like

= count(distinct if ( Sum(aggr (Sum([Debit/credit]) , [Contract]) <> 0, [Contract]))

[rubenmarin]

Hi, maybe:

count(distinct aggr(If(Sum([Debit/credit])<>0, [Contract]), [Contract]))

[sunny_talwar]

Try this for problem 1

Count(DISTINCT {<Contract = {"=Sum([Debit/credit]) <> 0"}>} Contract)

For Problem 2, what is the expected output?