topic Re: InMonths function giving strange results in QlikView

InMonths function giving strange results

shearwatertroy — Mon, 27 Jun 2016 15:53:51 GMT

Hey everyone,

I have a question regarding the inmonths function.

I'm trying to colour code sales from customers that are older that 6 months ago.

Here is what I'm using.

Equation:

if (inmonths (6, Max( [Order Date] ), Today(), -1), Red() )

Problem: January, February, March, April, May, and June of previous years are not being colour coded. I have attached a small screen shot Due to the number of records you can't see much of the previous information.

My source for inmonths function:

http://help.qlik.com/en-US/sense/2.2/Subsystems/Hub/Content/Scripting/DateAndTimeFunctions/inmonthstodate.htm

Re: InMonths function giving strange results

sunny_talwar — Tue, 28 Jun 2016 01:11:59 GMT

You want to color 6 months for this year and last year? May be this:

If(InMonths(6,Max([Order Date]), Today(), -1) or InMonths(6,Max([Order Date]), AddYears(Today(), -1), -1), Red())

Re: InMonths function giving strange results

shearwatertroy — Tue, 28 Jun 2016 19:25:11 GMT

This is close, but I want to set a user defined variable for how many months they want to go back.

Plus I want to highlight everything from 6 months back.

So if customer A hasn't purchased anything since December 2014 I want to be able to see that highlighted.

With the second part of your equation, it's limiting the scope to 1 year prior.

One solution I came up with was:

if( (Year (Max ( [Order Date] ) ) <> Year (Today () ) or ( inmonths ( varMonthLimit, Max ( [Order Date] ), Today (), -1) ) ) , Red() )

Which highlights everything NOT within this calendar year and X months back. Problem with this, is that in January, everything in December is highlighted red because it's not in the calendar year.