Hello community,
i have two tables that are connected via a key.
One table is a product and a date (2nddate) in a certain string format which represents the time it was produced. These dates are sortable.
The other table has another date (1stdate) in the same string format.
The dates in the examples are abc01, abc02 and abc03.
I want to show how many products were produced at which date (2nddate) in a bubble diagramm .
That is simple. Now comes the tricky part:
If I filter a date from the 1stdate collumn, i want the count of produced products above that date being added to the selected date.
For example product "product1" was produced in "abc01" 1 time, in "abc02" 2 times and in "abc03" 2 times. These dates are from the column 2nddate.
If i select "abc02" in 1stdate, the diagramm should show: abc01 1 time and abc02 4 times. So abc02 shows what abc02 + abc03 was.
If i select abc01 in 1stdate, the diagramm should show: abc01 9 times. (abc01 + abc02 + abc03)
First question is: how would you solve this problem?
Second question: somone showed me a possible solution: He created a dynamic dimension: "=if([2ndDate] > [1stdate], [1stdate], [2ndDate])" and somehow it works. After questioning him, he got confused too and couldn't explain how it works and he isn't even sure if it is right with all values. Do you have any idea why it works? When Qlikview calculates the values, does it pick the values from the 2nd date column, calculates them and labels it after 1stdate afterwards?
Is there a solution that preformes better? Its pretty slow with some data.
There is a little example attached where you can see in the first diagramm what the solution should be and the second shows where it comes from.
