topic Help with an expression in QlikView

Help with an expression

rittermd — Wed, 11 Feb 2015 20:34:17 GMT

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.

Re: Help with an expression

robert_mika — Wed, 11 Feb 2015 22:36:53 GMT

Could you post your data?

Re: Help with an expression

Oleg_Troyansky — Wed, 11 Feb 2015 23:32:11 GMT

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!

Re: Help with an expression

rittermd — Thu, 12 Feb 2015 00:41:51 GMT

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.

Re: Help with an expression

Oleg_Troyansky — Thu, 12 Feb 2015 00:55:32 GMT

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.