Count presence of an item in the next 4 months

[#1]

Hello all,

I'm trying to make a gauge in my application which will count how many application forms end in the next 4 months. Each application form has an end end, so technically i want to count how many dates appear from the current date up to 4 months in the future. As I'm new in Qlikview I find it a bit hard to write the right expression. At the moment I have:

Count({Period={ '>=$(Date=Addmonths(Max(Date#(Period,'YYYYMM')), +4), 'YYYYMM'))' }>}[Appl End Date])

which counts (something), but with not the right results.

Thanks in advance for your help.

[swuehl]

Is your Period of type date or is it a string?

If it is a string, try this

Count({Period={ '<=$(=Date(Addmonths(Max(Date#(Period,'YYYYMM')), 4), 'YYYYMM'))>$(=maxstring(Period)) ' }>} [Appl End Date])

if it is of type date (but maybe formatted to YYYYMM), maybe this:

Count({Period={ '<=$(=Addmonths(Max(Period), 4))>$(=max(Period)) ' }>} [Appl End Date])

I guess, in both cases you have more fields of type date, probably a date from which you generated the Period.

You might need to clear the selections for some / all of these date fields, because your set expression will keep the current selection, which might limit the set expression to a different selection than you intend.

For point in time reporting like this, see also

http://iqlik.wordpress.com/2010/11/27/the-magic-of-set-analysis-point-in-time-reporting/

{There is also explained why and how to clear those field selections}

I am not sure if I have all the brackets correct, that's a bit tricky and the syntax checker might not work correctly for the terms within the ' '.

What I essentially changed to your expression:

a)  removed the = behind the Date (I think you want to call the Date function), replaced with ( and out a = as start of the dollar sign expansion

b) changed the >= at the beginning to <=  and added a lower boundery ( I think you want Periods to be limited that way, if have understood you correctly)

Regards,

Stefan

[#3]

Thanks for you answer! I checked the blog and god was I way off! I rewrote the code and now I have

Count({1<[Appl End Date]={CODE HERE}>}[Appl ID])

This one works ok if I pute specific dates where the CODE HERE is or if I put something like CODE HERE='>=03/07/2011'. What I can't do though is get the current date plus 4 months and put it where the code is, so it would bring back ids up to 4 months in the future. Any ideas?

[swuehl · Accepted solution]

Maybe

CODEHERE = "<=$(=addmonths(today(),4))>=$(=today())"

(I use double quotes indicating search string)

to get date range between today and four months from today.

Stefan

[#5]

Worked like a charm Thanks a lot! To get something straight, why did you put =$(=today()) at the end?

[swuehl]

Do you mean why I put it at the end and not started with?

I think it doesn't matter.

Or why I put it in the expression at all?

I you remove the lower boundery, you will count all Appl IDs with end date before four month from today, also the ones from the past.

Have fun,

Stefan

[#7]

Ok got it. Thanks again