topic Problem with line showing for future days when using full accumulation in QlikView

Problem with line showing for future days when using full accumulation

Sat, 12 Feb 2011 16:48:51 GMT

Hi all,

In the above chart I have a quarterly forecast vs QTD actuals, and as the QTD actual is accumulated it is showing across future days. Any ideas how I can prevent this?

Thanks very much,

James

Problem with line showing for future days when using full accumulation

Sat, 12 Feb 2011 18:00:38 GMT

I was able to get this to work by using changing the background colour to transparent for future days, however this does spoil the legend.

Any other ideas?

Thanks,

James

Problem with line showing for future days when using full accumulation

Sat, 12 Feb 2011 19:43:59 GMT

One way would be to set a variable to the max date of the actuals using load resident and peek.

tempTable: load max(Date) as maxdate resident actuals;

let MaxDate = peek('maxdate');

drop tempTable;

Then use the variable to control the expression.

=if(Date<MaxDate,sum(actuals))

Problem with line showing for future days when using full accumulation

Mon, 14 Feb 2011 22:24:36 GMT

Dont believe that works any other suggestions?

Problem with line showing for future days when using full accumulation

johnw — Mon, 14 Feb 2011 23:49:47 GMT

See attached. The first chart shows the transparent background color problem, where the legend is in gray instead of the correct color. The second chart handles the accumulation with the rangesum() and above() function. The third chart is I think the most robust solution, but also perhaps the most complicated. It uses a generated AsOf table that links an AsOfDate to all previous dates in the data model, allowing QlikView's associative logic to handle the accumulation. Both of the second two charts use an if() around the expression to only calculate it for today() and earlier.

Problem with line showing for future days when using full accumulation

Tue, 15 Feb 2011 13:07:19 GMT

Thanks all, particularly John for your excellent demo.