At first, I wanted to add dynamically this values while loading, to be able to play with this values as axis.
Here is my first problem : I don't know how to use the "SUM" function in the "LOAD" section. Is it at least possible ?
(I can use the "peek" function, but it not helps for what I want to do...)
Next, I tryied to calculate those values "on the fly". I've created a simple table (graph), in wich I set "Date" as dimension, and "=Value" as expression. I want a to add another expression that says "the sum of Values in the lines having the same year" and onther one that says "the sum of Values from the 4 past months".
I added a field "Year" in my load : [CODE] Sells2: LOAD *, left(Date, 4) AS [Year] RESIDENT Sells ORDER BY Date; [/CODE]
If I add
=sum({$<Year<={"2008"}>} Value)
I have the expected values, but only for the year "2008" (obviously).
Unfortunately, there's no such thing as THE_YEAR_CORRESPONDING_TO_THE_YEAR_OF_THIS_LINE in set analysis. Set analysis is performed ONCE for the entire chart. You can't define a separate set of data for each line of the chart, which is what you're trying to do here.
Here are two approaches. The first approach does the accumulation in the script. I'm not happy with the four month accumulation, since it assumes you only have one record per month. The YTD logic is more robust. Both fields required that the records be in ascending order by date. The second approach does the accumulation in the chart. It uses a month completely separated from your main data set as the dimension, and then uses IF statements to sum the correct months for each row of the chart.
The value I want is "the sum from the begining of the year to the current date".
IE :
For the January 2008 month, I want "values of Jan 2008". For the February 2008 month, I want "values of Jan 2008 + values of Feb 2008". For the March 2008 month, I want "values of Jan 2008 + values of Feb 2008 + values of Mar 2008". ... For the Decembre 2008 month, I want "values of Jan + Feb + Mar + Apr + May + Jun + Jul + Aug + Sep + Oct + Nov + Dec 2008".
Unfortunately, there's no such thing as THE_YEAR_CORRESPONDING_TO_THE_YEAR_OF_THIS_LINE in set analysis. Set analysis is performed ONCE for the entire chart. You can't define a separate set of data for each line of the chart, which is what you're trying to do here.
Here are two approaches. The first approach does the accumulation in the script. I'm not happy with the four month accumulation, since it assumes you only have one record per month. The YTD logic is more robust. Both fields required that the records be in ascending order by date. The second approach does the accumulation in the chart. It uses a month completely separated from your main data set as the dimension, and then uses IF statements to sum the correct months for each row of the chart.
I should probably point out some weaknesses of the separate month approach. First, performance. QlikView is essentially looping through the months, then pulling in ALL months of data to compare to each. That's a lot of work for it to be doing, and on large data sets, the report will be noticeably slow. It may perform just fine on smaller data sets. Second, ease of use issues. If you select the month from the report, you aren't selecting the real month, only the fake month. So while that report will change, no other reports will change. Even worse, if you select the real month, the chart will still show multiple months - every month of data that the one month of data will be accumulated into.
I think I have a third approach that handles it in the data model without actually doing accumulations ahead of time. I'll need to work it into the example, but it would be loosely based on the attached approach for showing a previous year's data. The basic idea is that every record has a unique ID, and then you create a table that joins one selected month to the actual records that span multiple months. If I can't get to it quickly, maybe you can work it out with that hint and the example file if the weaknesses of the separate month approach matter in your application.
Here's the original example extended to use the linkage table approach. Using FOR loops is probably the most efficient from a memory point of view, but there's probably a much faster approach using JOIN. I've replaced FOR loops in the past with JOINS because time to load has been more critical around here than memory use. But that's really a separate issue, so I'm leaving it as is since it appears to work.
