I am new to Qlikview and have possibly run into a problem with Synthetic keys. Please skip to the bottom if you want the short question and Excel file.
We have four tables. For two of them, Services and Contracts, we have generated a synthetic key.
The primary fields involved are 1) a Market Sector hierarchy of Sector -> Segment -> Sub-Segment; 2) Customer hierarchy of Customer -> CustomerAgency; and 3) Company hierarchy of Company -> Company Operating Unit. Finally, there is Year and Amount.
The Services table has 1), but for 2) it only has Customer (not CustomerAgency), and 3) it only has Company (not Company Operating Unit).
It may seem redundant to have two separate tables; the primary reason for this is that the Services table includes our forecasts past 2010-2013, and because it is a simplified client deliverable (the Contracts table is raw historical data with 40+ fields).
Anyway, the problem we have is that we cannot get Amounts to match between Services and Contracts when we specify a CustomerAgency or Company Operating Unit in the Contracts table. What we thought would happen is that Qlikview would automatically roll-up to show all Amount values in the Services table associated with the Company, because the Services table does not include Company Operating Unit. It is okay if the two tables did not match, but at least provided an accurate roll-up.
Instead, we found that when we selected a CustomerAgency or Company Operating Unit in the Contracts Table, the Services table rolled-up some data, but not all. I.e. if we selected Company X - Unit 1 in the Contracts table, we did not get all of Company X in Services.
We played with the data in Excel and found that in the Contracts table, when we specified an Operating Unit or Customer Agency, combinations of data that had Amount as $0.00 seemed to prevent values in Services from being rolled-up. We did this comparison by adding a unique ID column, QA_ID.
Question: How can I get Qlikview to not filter out synthetic keys associated with values of $0.00 in the Contracts table? Please take a look at my Excel example. In the Contracts tab, we filtered by "Company X: Unit 1" in column E. In the Services tab, column I, "Amount", shows in green the values that were rolled up, while red shows values that were not. Using the column "QA_ID", it is obvious that $0.00 values affected the roll-up across tables. Can you explain why? Thank you!
Thanks for your response. From what I understand, this is a data issue, not a charts issue. After applying filters, we pulled the data straight from the tables, and saw that the Services table had not rolled up every row in the Sector.