Problem with line showing for future days when using full accumulation

[#1]

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

[#2]

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

[#3]

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))

[#4]

Dont believe that works any other suggestions?

[johnw · Accepted solution]

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.

[#6]

Thanks all, particularly John for your excellent demo.