Self reference calculation

[JeroenHoltrop]

Hi,

I am trying to setup an overview where Qlik does a calculation based on a self-reference. Below a table where the right column is the calculated value. 

Invoicenumber	OverallInvoiceNumber	amount	summedamount
1	4	10
2	4	20
3	4	30
4		40	-60

So, when there is an overallInvoiceNumber filled, the summed amount is 0. That is easy :). However, in the same overview , if there is an invoice which is referred to in the overallinvoicenumber, I want to sum all the amounts of the invoices that have that number in the reference. This should be independent of the selection, so when I have a selection where Invoice 4 shows, but 1-3 not, the value of the summed amount still needs to be -60. 

My thought is something like this, but it does not work: 

=sum({1<GroupInvoiceNumber={$<InvoiceNumber>}>} InvoiceNetAmount) 

I want to sum all Groupinvoicenumvbers that are referenced in the current selection Invoicenumber, at least that is my thought. 

Hope you understand what I am trying to achieve and able to help me. 

Thanks a lot!

[marcus_sommer]

You may try:

sum({<InvoiceNumber=p(OverallInvoiceNumber)>} InvoiceNetAmount) 

but I assume that this will only work in some specific scenarios - for example not many OverallInvoiceNumber are available and the views and the further selections aren't not divers and/or complex.

I think I would tend to calculate it within the script, for example with something like this:

t: load * from source;
left join(t) load OverallInvoiceNumber as InvoiceNumber, sum(InvoiceNetAmount) as X resident t;

- Marcus

[JeroenHoltrop]

Hey Marcus, 

Thanks a lot fot your effort. I tried this and indeed the formula returns a 0, sadly. I have a couple of fallback scenarios which I can present to the business, but also for future similar cases, I am not giving up yet to try to achieve it without changing the load script.  I can foresee many of these wishes coming from the business in the near future, so if I am able to do this in Qlik, I am the most flexible. 

Cheers

[JeroenHoltrop]

btw, I have found something:. When I select an invoice and fill it in the script, it at least gets the InvoiceNetAmount for all the referenced invoices. However, Qlik doesn't sum it for some reason, but simply shows all the referenced invoices 

=sum({1<OverallInvoiceNumber={1938890}>} InvoiceNetAmount)

[marcus_sommer · Accepted solution]

I could not recommend to try to calculate such things within the UI else building a suitable datamodel for it which at least provides the main-matching for those views - means it must not be mandatory necessary to calculate it completely within the script but creates the proper associations.

In general set analysis worked like a selection - the important question for you is could you get your expected calculations/views just with selections on your existing datamodel. Even if you could ignore and/or overwrite the set selections with the set analysis and/or combining multiple ones and/or using TOTAL to ignore the dimensionality within your charts and/or applying aggr-constructs to overcome the lack of proper associations in the datamodel - it requires quite complex logics and syntax, has often disadvantages in the usability and the performance will be definitely not so well as with the native field-selection logic.

Therefore I suggest to do the main-work within the datamodel - if you don't want to calculate the results there you should in each case associate the data in any way. For it you may just concatenate InvoiceNumber and OverallInvoiceNumber into a single field or applying an The As-Of Table - Qlik Community - 1466130 approach or any other method.

- Marcus    

[JeroenHoltrop]

Hey Marcus,

The total indeed did the trick here indeed, I now have the overview in general that I need. Thanks also for taking the time to educate me more. I understand the issue of the performance and if that is going to be blocking, I indeed will switch to a different method. The reason why I am a little hesitant for this, is that we have a single Qlik solution for multiple counties backoffices. This is a specific request for 1 country, where I am hesitant to "polute" the datamodel with country-specific needs. 

Your help is greatly appreciated!

Cheers, Jeroen