Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Take max date with a value for actual and forecast comparison

Hi!

I currently have a line chart with two expressions over a date dimension, (1) Actual and (2) forecast sales data for 2017.

I want to show the current '% of forecast' (actual to date divided by forecast to date) in a text box above the chart. My problem is that by simply dividing actual sales by forecast, I have a whole years' worth of data (all of 2017) in the denominator, and only year-to-date actual data, creating an incorrect number.

This problem resolves if I filter the data correctly, however I'm after a text box which shows this accurately without filtering, and also updates when selecting just Jan, for instance (and displays Jan Actual divided by Jan Forecast).

I think I need to take the max date with a value, and have this set as a 'stop' so I don't over-divide.

I'm pretty sure this is possible with some Set Analysis, however I'm not sure about how to do this

Thank you for your help in advance!!!

3 Replies

Re: Take max date with a value for actual and forecast comparison

So when you were doing this using selection, did you select all the months upto the actual month or just by selecting the max month, you were able to get the right %age?

Not applicable

Re: Take max date with a value for actual and forecast comparison

All Months up to the max month, i.e. I select from 01/01/2017 to yesterday, and that gives the correct answer.

Thanks for your question!

Re: Take max date with a value for actual and forecast comparison

May be like this for forecast

Sum({<Date = {"$(='>=' & Date(YearStart(Max(Date))) & '<=' & Date(Max({<Actual = {'*'}>}Date)))"}>}Forecast)