Hi All,
I don't really have an issue to be solved, but more of a question about the background logic of Qlik.
The issue:
I had to create a timeline chart with a cycle where the user can switch between week, month, quarter and year. I am working with snapshot/ point n time data, so for each period I had to get the value from the last date of the respective period. I addition, if the user is looking at the year view and selects a period before the year end date, the values from the last date from the selected period should be shown in the chart. My calendar is in a different table and links to my fact table through a surrogate key field for the date. In order to identify the last date for each period I am using the max(refdate), which works perfectly when you add it in an expression or calculated dimension, but not when you try to put it in a set analysis.
My solution:
I created an aggregation for the max(refdate) by the current field from the cycle group, which looked like that:
aggr(max(REFDATE),$(vTimeSeries)), where vTimeSeries = getcurrentfield(CycleGroup)
So far so good, but my numbers would not align. After some digging I found out that for some reason the calculation was applied to the wrong date. Some more digging later, it turned out that the calculation was always applied to the date that was first loaded. After doing an order by refdate desc in the load script of my qvd generator, all was finally aligning.
My question:
Can anyone help me understand the logic that is embeded in the background that would explain the above described behavior? Why did I have to sort my source data in order to get the needed results?
Thanks,
Thea
