Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problem with line showing for future days when using full accumulation

error loading image

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

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

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.

View solution in original post

5 Replies
Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

Dont believe that works any other suggestions?

johnw
Champion III
Champion III

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.

Not applicable
Author

Thanks all, particularly John for your excellent demo.