Aggr spend on Accounts and count Accounts with <>0

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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 !

Re: Aggr spend on Accounts and count Accounts with <>0

vishsaggi — Fri, 15 Jun 2018 16:38:13 GMT

Try like

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

Re: Aggr spend on Accounts and count Accounts with <>0

rubenmarin — Fri, 15 Jun 2018 17:08:25 GMT

Hi, maybe:

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

Re: Aggr spend on Accounts and count Accounts with <>0

sunny_talwar — Fri, 15 Jun 2018 19:06:04 GMT

Try this for problem 1

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

For Problem 2, what is the expected output?

topic Re: Aggr spend on Accounts and count Accounts with <>0 in QlikView

Aggr spend on Accounts and count Accounts with <>0

Re: Aggr spend on Accounts and count Accounts with <>0

Re: Aggr spend on Accounts and count Accounts with <>0

Re: Aggr spend on Accounts and count Accounts with <>0