topic Re: Actual vs Forecast negative values in QlikView

Actual vs Forecast negative values

clondono — Tue, 12 Apr 2016 23:25:17 GMT

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!

Re: Actual vs Forecast negative values

Oleg_Troyansky — Tue, 12 Apr 2016 23:48:28 GMT

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

Re: Actual vs Forecast negative values

clondono — Wed, 13 Apr 2016 00:29:14 GMT

Yes Oleg, set analysis did the trick!

Awesome, thanks for your help!

Carlos

Re: Actual vs Forecast negative values

clondono — Wed, 13 Apr 2016 00:30:58 GMT

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

Re: Actual vs Forecast negative values

Oleg_Troyansky — Wed, 13 Apr 2016 17:53:33 GMT

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