Actual vs Forecast negative values

[clondono]

Hello,

I have a chart to show Actual vs Forecast amounts (see attached sample).  The issue I am having is that the forecast data is showing negative values for future months when in reality it should not contain any negative values for future months.  We have a flag for current forecast (IS_CURRENT_FORECAST), all values flagged 'Y' should be future months (in this case March-June 2016 because data for March has not been consolidated yet).

Dimension Year/Month: 

=Date(FISCAL_MONTH_YEAR, 'YYYY-MMM')

Expressions:

Forecast:

round(sum(FORECAST_AMOUNT), 1000)/1000 

Actual:

round(sum(ACTUAL_AMOUNT), 1000)/1000 

Thanks for your help!

[Oleg_Troyansky · Accepted solution]

Hi Carlos,

the data clearly shows a huge negative amount for June. It's marked as not current. Maybe you need to add a Set Analysis filter and only select "current" forecast? Something like this:

round(sum({<IS_CURRENT_FORECAST = {'Y'}>} FORECAST_AMOUNT), 1000)/1000

See attached solution.

cheers,

Oleg Troyansky

Check out my new book QlikView Your Business - The Expert Guide for QlikView and Qlik Sense

[clondono]

Yes Oleg, set analysis did the trick!

Awesome, thanks for your help!

Carlos

[clondono]

Definitely I will be getting your book. Is it available on Packt Publisher or just Amazon?

[Oleg_Troyansky]

Hi Carlos,

It's published by Wiley, so it wouldn't be available from Packt, but other than that - any common book store has it in a variety of formats.

enjoy!

Oleg Troyansky