Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All – a self taught Qlikview wannabe here struggling with data that creates loops – any advice would be well received!
I have several sets of financial data which I am trying to compare to give me comfort that the output of a process is what I’d reasonably expect it to be.
The actual data consists of a set of financial data including cost centre, legal entity and country. The calculated source data includes the same but is then separated appropriately, put through 4 separate processes and aggregated to give the final result – the comparison between actual and calculated can then be made by legal entity and country.
The problem is that when I try to aggregate and link the calculated data by more than one dimension to the actual data I get loops.
Has anyone got any ideas about the best way to either:
a) Model the data appropriately or
b) What functions would be best suited to help me aggregate the data?
Many thanks.
Without seeing your data, it is difficult to give a definite answer, but my guess is that you should concatenate the actual numbers with the calculated, so you get them in one table.
See also
http://community.qlik.com/blogs/qlikviewdesignblog/2013/06/25/circular-references
HIC
Hi Henric,
Thanks for your response.
I am unable to load a file due to my company firewall but the data would look something like this:
Input: | Output: | ||||
Legal Entity | Country | Cost Centre | Actual Financial (EUR) | Calculated Financial (EUR) | Variance |
UK1 | United Kingdom | UKA | 112 | 82.5 | 29.5 |
UK2 | United Kingdom | UKB | 56 | 102.5 | -46.5 |
NL1 | Netherlands | NLC | 49 | 35 | 14 |
US1 | United States | USA | 84 | 65 | 19 |
US2 | United States | USB | 49 | 65 | -16 |
Cost Centre Source Financial (EUR) | |||||
Cost Centre | Financial (EUR) | ||||
UKA | 100 | ||||
UKB | 50 | ||||
NLC | 50 | ||||
USA | 100 | ||||
USB | 50 | ||||
Process 1 | To: | ||||
Cost Centre | Rate | Legal Entity | Country | Calculated Output | |
UKA | 50% | UK1 | United Kingdom | 50 | |
UKA | 50% | UK2 | United Kingdom | 50 | |
Process 2 | To: | ||||
Cost Centre | Rate | Legal Entity | Country | Calculated Output | |
UKB | 30% | UK1 | United Kingdom | 15 | |
UKB | 70% | UK2 | United Kingdom | 35 | |
NLC | 70% | NL1 | Netherlands | 35 | |
NLC | 15% | UK1 | United Kingdom | 7.5 | |
NLC | 15% | UK2 | United Kingdom | 7.5 | |
Process 3 | To: | ||||
Cost Centre | Rate | Legal Entity | Country | Calculated Output | |
USA | 45% | US1 | United States | 45 | |
USA | 45% | US2 | United States | 45 | |
USA | 10% | UK1 | United Kingdom | 10 | |
Process 4 | To: | ||||
Cost Centre | Rate | Legal Entity | Country | Calculated Output | |
USB | 40% | US1 | United States | 20 | |
USB | 40% | US2 | United States | 20 | |
USB | 20% | UK2 | United Kingdom | 10 |
First of all, you can put all process data in one table. E.g.
Load *, 'Process1' as Process From Process1;
Concatenate
Load *, 'Process2' as Process From Process2;
etc.
Secondly, you have cost centre, legal entity and country in two tables, so you might need to create a composite key from these:
[Cost Centre] & '|' & [Legal Entity] & '|' & Country as Key
and just keep the separate fields in on of the tables: either the process table or the input table.
Then it ought to work.
HIC