Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

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

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!

May be like this for forecast

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