I have several variations of a pivot table table that I'm trying to create specific calculations for the most current years as totals rows and after reading posts all morning I'm not even sure it can be done. The Excel attachment is the representative report I'm trying to acvhieve. It contains monthly Sales columns with yearly rows by region. I need to show a row with the variance for the last two years and another row with the perecntage variance for each region. Any help would be greatly appreciated.
Ugh, yeah, I tried building it for your data set, and I think I see what you mean about needing to concatenate everything to the Year field individually. That is ugly. The attached example avoids that, but it has a different down side. It's using the Chart Year as a data island disconnected from the main data. That means you have to link your data to the chart's dimensions manually, which is much slower than allowing QlikView to do it automatically. But at least you only need to create the fake values once.
I'm definitely not thrilled with this solution either. On top of the possible performance issues, this just feels like it should be simpler.
Ugh, yeah, I tried building it for your data set, and I think I see what you mean about needing to concatenate everything to the Year field individually. That is ugly. The attached example avoids that, but it has a different down side. It's using the Chart Year as a data island disconnected from the main data. That means you have to link your data to the chart's dimensions manually, which is much slower than allowing QlikView to do it automatically. But at least you only need to create the fake values once.
I'm definitely not thrilled with this solution either. On top of the possible performance issues, this just feels like it should be simpler.
It works but when I concatenated the Region and Year in the valuelist I ran out of object memory. It looks like I'm going to need to rework my script to get any performance out of the chart requiement. I'll use your table 3 qvw as a guide and build a the fake dimension.
