Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

Count presence of an item in the next 4 months

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: Count presence of an item in the next 4 months

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

6 Replies
MVP
MVP

Count presence of an item in the next 4 months

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

Not applicable

Count presence of an item in the next 4 months

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?

MVP
MVP

Re: Count presence of an item in the next 4 months

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

Not applicable

Count presence of an item in the next 4 months

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

MVP
MVP

Count presence of an item in the next 4 months

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

Not applicable

Count presence of an item in the next 4 months

Ok got it. Thanks again