Hi there!
This is exactly the topic that I will be teaching in a couple of hours at the Masters Summit for Qlik in Washington, DC 🙂 I wish you could be here, you'd know everything by the end of the day. Let me give you a few directions to explore:
- You don't see the expected results because your AGGR dimensions are less granular than your Chart Dimensions. This is what I call in my book "The third law of AGGR", and what Henric Cronstrom calls the "grain mismatch" problem. Add all the dimensions of the chart into your formula, and you should be able to see the expected results in every line.
- I'm not sure I understand your requirement about "the oldest" record should be considered. Maybe you could define it with a conditional flag, or formulate a Set Analysis filter that only selects the oldest record. Another good function to explore here is FirstSortedValue - maybe that is what will produce the desired result.
- I don't quite understand the need in the AGGR function. Both of your aggregation functions (internal and external) are Sums. What kind of a special logic are you trying to achieve with the use of AGGR?
Cheers,
Oleg Troyansky
Check out my book QlikView Your Business - the expert guide to QlikView and Qlik Sense.
