topic Re: Compare data from two dates in QlikView

Compare data from two dates

Wed, 29 Feb 2012 14:06:41 GMT

Hi,

I wish to compare forecasts from two different forecast versions,

I have a table which basically is design as such:

ForecastDate	SalesDate	Forecast
2012-01-01	2012-03-01	100
2012-01-01	2012-04-01	80
2012-01-01	2012-05-01	90
2012-02-01	2012-03-01	100
2012-02-01	2012-04-01	100
2012-02-01	2012-05-01	100

I have created a variable called MaxForecastDate defined as Max(ForecastDate). In this case, it returns '2012-02-01', and is used to determine the latest forecast version.

I have then created a chart with dimension SalesDate and expression:

=SUM({<GSFDate={'2012-02-01'} >} Forecast)

This works as expected, but I would like that the chart is dynamically updated when a new ForecastDate is added. To compare forecasts from the two forecast dates, I am planning to use the function AddMonth(MaxForecastDate, -1) .

Any help would be very appreciated!

Best regards

Martin

Compare data from two dates

CELAMBARASAN — Wed, 29 Feb 2012 14:10:51 GMT

Hi,

You can try with this

=SUM({<GSFDate={'$(MaxForecastDate)'} >} Forecast)

Celambarasan

Compare data from two dates

jagan — Wed, 29 Feb 2012 14:15:10 GMT

Hi,

Use this

=SUM({<GSFDate={'$(=(AddMonth(MaxForecastDate, -1)))'} >} Forecast)

Regards,

Jagan.

Compare data from two dates

Wed, 29 Feb 2012 14:21:43 GMT

Perfect, I was trying with " istead of '...

Now I am trying to subtract a month,

=SUM({<ForecastDate={'$(AddMonth(MaxForecastDate, -1)'} >} Forecast)

Compare data from two dates

Wed, 29 Feb 2012 14:24:24 GMT

Hi Jagan

This is not returning any data. I can use AddMonth(MaxForecastDate, -1) as an expression which correctly returns '2012-01-01', but not when used in the set analysis.

Re: Compare data from two dates

jagan — Wed, 29 Feb 2012 14:28:51 GMT

Hi,

Create another variable with

=AddMonth(MaxForecastDate, -1)

and use this new variable in the expression as you did previously

Regards,

Jagan.

Compare data from two dates

Wed, 29 Feb 2012 14:34:59 GMT

I found the reason for the behaviour. AddMonth('2012-02-01', -1) returned '01-01-2012', which confused the set analysis. By applying the Date() function by Date(AddMonth('2012-02-01', -1), 'yyyy-MM-dd')