topic Re: Timing Issue in QlikView

Timing Issue

clondono — Fri, 08 Apr 2016 20:06:56 GMT

Hello,

I need to calculate the actuals against the most recent forecast. In the data I have actuals for the current month but since the month is in progress, I need to exclude current month values from my chart. The logic should be:

Show actuals when the most current forecast (using FORECAST_DATE) no longer includes forecast data for that month.

I'm using the following expression but I can't get the correct results:

=if((Max(Date(FORECAST_DATE, 'YYYY-MM')))< (Date(Today(), 'YYYY-MM') AND (FORECAST_AMT = 0), (sum(ACTUAL_AMOUNT)),0)

Thanks for your help!

Re: Timing Issue

sunny_talwar — Fri, 08 Apr 2016 20:12:47 GMT

May be this:

If(MonthStart(Max({<FORECAST_AMT = {0}>} FORECAST_DATE)) < MonthStart(Today()), Sum(ACTUAL_AMOUNT), 0)

I am not sure if the above is going to work because I am not sure what you dimension is and how your data is structured. If it doesn't work, I would suggest providing a sample

Re: Timing Issue

clondono — Fri, 08 Apr 2016 20:57:03 GMT

Thank you Sunny, let me give this a try and I will let you know if it works, otherwise I will upload a sample file.