Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Set analysis to get records related to dimension in chart in a multilevel aggregation

Dear Qlik Fellows:

I am struggling with an interesting case of a related lookup.

Here is my table: 1 DocumentNumber contains >=1 Accounts.

DocumentNumberAccount
1Accounts Receivable
1Duties
1Tax
2Accounts Receivable
2Unrecoverable Debt

Now, I need to display an overview, by Account, how many other Accounts occur under the same DocumentNumber with this Account. I need a table like this: 

AccountNr of other Accounts occurring in the documents containing this accountHow is this Nr of Accounts obtained?
Accounts Receivable3This account is mentioned in DocumentNumber 1 and 2, and there are 3 other accounts in these documents apart from this one
Duties2This account is only mentioned in DocumentNumber 1, and there are 2 other accounts in this document apart from this one
Tax2This account is only mentioned in DocumentNumber 1, and there are 2 other accounts in this document apart from this one
Unrecoverable Debt1This account is only mentioned in DocumentNumber 2, and there is only 1 other account in this document apart from this one

So, for each Account dimension value:

  1. Get every DocumentNumber that contains this Account
  2. Get every Account in the DocumentNumber set that is not equal to the Account on the row
  3. Count distinct Accounts

Tried this, did not succeed:

=Count({<DocumentNumber=P({$}DocumentNumber), Account=E({$}Account)>}distinct Account)

I have a feeling there should be an Aggr with set analysis. But I am struggling to formulate the logic described above (get the possible DocumentNumbers, then get all Accounts from this set excluding the one in the dimension row).


Attached my Sense exercise.


Thank you

Igor

9 Replies
sunny_talwar

May be this

=Sum(Aggr(Count(total <DocumentNumber> DocumentNumber), DocumentNumber, Account)) - Count(Account)

Capture.PNG

Anonymous
Not applicable
Author

Kudos to Sunny, very cool! It works as expected!

I have only modified the formula with {1}, and now it works with filters as well

=Sum(Aggr(Count({1} total <DocumentNumber> DocumentNumber), DocumentNumber, Account)) - Count(Account)

2017-10-03_142801.png

Anonymous
Not applicable
Author

Actually found a problem with this formula now

I added another document to the sample, and the formula started to double count.

Here you see DocumentNumber 3.   

DocumentNumberAccount
1Accounts Receivable
1Duties
1Tax
2Accounts Receivable
2Unrecoverable Debt
3Accounts Receivable
3Duties
3Unrecoverable Debt

I would expect to get 4 for Account = Accounts Receivable. But it is not really counting distinct.

2017-10-03_145816.png

sunny_talwar

Why 4? Why not 3? Isn't Duties getting double counted also?

sunny_talwar

My bad, why is Unrecoverable Debt getting double counted (3 and 4)?

Anonymous
Not applicable
Author

Thanks for pointing out, I made a mistake here.

Here's the issue:

For Account that occurs in more than one DocumentNumber in the data, there is double counting. E.g. Accounts Receivable should have 3 related Accounts (Duties, Tax, Unrecoverable Debt), but there are 5.

sunny_talwar

Probably not the best way in the world to do this... but try this

=SubStringCount(Concat(DISTINCT Aggr(SubField(Concat(Aggr(nodistinct Concat({1} distinct total <DocumentNumber> Account, ','), DocumentNumber, Account), ','), ',', SNo), Account, SNo), ','), ',')

Where SNo is created in the script like this and will depend on how many DocumentNumber/Account combination are there. In this small example, the maximum was 8, but this can very easily be more than that. I would use a bigger number like 10,000 or 15,000 here... but this will somewhat impact performance

SNo:

LOAD RowNo() as SNo

AutoGenerate 20;

Anonymous
Not applicable
Author

Thanks for the tip Sunny.

My production table contains 360k records, and I would like to minimize any load script actions, so the approach you suggested is not ideal. I think the initial Aggr that you suggested actually looks very promising. It gave pretty close results, and I will continue playing around with it. I'll post here if/when I win.

sunny_talwar

All you need in the script is this

SNo:

LOAD RowNo() as SNo

AutoGenerate 20;

This table is not even going to be attached to anything else in your database (it should be an Island Table).... even adding this table is going to be an issue?

Ya, do let us know if you figure something out