Help with an expression

[rittermd]

I have written this expression to find the difference between two dates and then group the records by the number of months difference.

So 0-3 months, 4-6 months, etc.

This expression seems pretty simplistic.  However, when I use this expression in a list box I am not getting the expected results. 

For example the range 0-3 is also displaying those that return a value of -1.  All else is working correctly.  I just don't see what is wrong.

I created a dimension to display the results of the formula and that part is working correctly based on the data.  The other strange thing is that when I pick one of these ranges from the list box multiple values are highlighted (white) indicating that I have an overlap for some reason.

=if((((((YEAR(DueDate)-PeriodYear)*12+(MONTH(DueDate)-PeriodMonth)))>=0 AND measure_result='1')

        AND (((YEAR(DueDate)-PeriodYear)*12+(MONTH(DueDate)-PeriodMonth))<=3 AND measure_result='1')),'0-3 months',

        if(((((YEAR(DueDate)-PeriodYear)*12+(MONTH(DueDate)-PeriodMonth)))>=4 AND measure_result='1')

        AND (((YEAR(DueDate)-PeriodYear)*12+(MONTH(DueDate)-PeriodMonth))<=6 AND measure_result='1'),'4-6 months',

        if(((((YEAR(DueDate)-PeriodYear)*12+(MONTH(DueDate)-PeriodMonth)))>=7 AND measure_result='1')

        AND (((YEAR(DueDate)-PeriodYear)*12+(MONTH(DueDate)-PeriodMonth))<=12 AND measure_result='1'),'7-12 months',

        if((((YEAR(DueDate)-PeriodYear)*12+(MONTH(DueDate)-PeriodMonth)))<0 AND measure_result='1','*Due Now*',))))

Also, if there is a better way to do this please let me know.  I am new to QlikView and don't understand all of the functions yet.

Thank You.

[robert_mika]

Could you post your data?

[Oleg_Troyansky]

There has to be a simpler way of calculating this one, like this:

IF(DueDate > AddMonths(CurrentDate, -3) and DueDate <= CurrentDate, '0-3 months',

IF(DueDate > AddMonths(CurrentDate, -6) and DueDate <= AddMonths(CurrentDate, -3) , '0-3 months',

...

I may be reversing the direction, but this is the general direction. If you want to compare whole months, you may use MonthStart() with one or both dates.

It's always much easier to perform date math with dates fields than tinker with Years and Months separately.

cheers,

Oleg Troyansky

Come and learn Set Analysis and Advanced Aggregation with me at www.masterssummit.com - take your QlikView skills to the next level!

[rittermd]

Part of the problem is that one field is a date and the other is just YYYYMM.  So I have to break the YYYYMM into its parts and then calculate against the year and month in the date field. 

[Oleg_Troyansky]

Mark,

you can easily convert that to a date. For example:

MakeDate(Year, Month) will generate a date corresponding to the beginning of the same month.