Hello All

[ektarathi]

I am using this condition in a piece off code

If(dates>='$(Last12MonthsVar)' and dates<='$(PreMonthVar)','Last 12 Months') as 'Last 12 Months',
If(dates>='$(Last6MonthsVar)' and dates<='$(PreMonthVar)','Last 6 Months') as 'Last 6 Months',
If(dates>='$(Last3MonthsVar)' and dates<='$(PreMonthVar)','Last 3 Months') as 'Last 3 Months'

where PremonthVar = 30 Jun 2012

Last3MonthsVar----- 1 Apr 2012
Last6MonthsVar------ 1 Jan 2012
Last12MonthsVar------ 1 July 2011

date is like month end date for each month like 2011-01-31, 2011-02-28.......2012-01-31...so on....and the last available date is
2012-06-30.

but when i see data for last 3 months or last 6 months or last 12 months, 30 Jun 2012 never gets  selected
and I can see data only till 2012-05-31 all the time.
why is it so when I have used less than or equal to in the codition?how do I need to compare the date to get the data of 30 Jun 2012  as well.

[fosuzuki · Accepted solution]

Hi,

The 'dates' field has dates with time (hour/minute/second)? And your variables, do they also have hour/minute/second?

If the dates field has dates like'30 Jun 2012 09:43 AM', and if the PreMonthVar has a date like '30 Jun 2012' (without time), then this is the problem... QlikView is treating the PreMonthVar as '30 Jun 2012 00:00', which is less than '30 Jun 2012 09:43 AM' (for example).

Hope this helps you.

Fernando

[ToniKautto]

It is really hard to give advise on issues like this without a sample QVW, as it is relvant to evaluate the actual data you are using.

Keep in mind that date/time values have a textual presentation and a underlying numerical value. Generally it is advisable to make sure all the values you are working with have been properly formatted as dates or timestamp, meaning that the underlying numerical value is either an integer for date or a decimal value for timestamps. Once you have formatted the values properly you can compare the numerical value instead of the text values as in the example above.

[ektarathi]

Hi Fernando,

You are correct..i was using monthend function to create field "dates" and that is why a timestamp got associated with it..something like '2012-06-30 23:59:59:999', and I was cmparing this with '2012-06-30' and that's why my logic was failing.

Thanks a lot..It really helped!!!

[ektarathi]

Hi Fernando,

You are correct..i was using monthend function to create field "dates" and that is why a timestamp got associated with it..something like '2012-06-30 23:59:59:999', and I was cmparing this with '2012-06-30' and that's why my logic was failing.

Thanks a lot..It really helped!!!