Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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?
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
Worked like a charm Thanks a lot! To get something straight, why did you put =$(=today()) at the end?
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
Ok got it. Thanks again