topic Set analysis expression syntax in QlikView

Set analysis expression syntax

georgina_spary — Fri, 27 May 2011 15:06:42 GMT

Hi folks - I hope you can help me

In my table showing counts of applications to date, I want to add another showing applications up to same day last year

I've created a variable v_LastAppYear, as Daystart(AddMonths([DM_Refresh_Date], -12)) works fine in a text box and displays

27/05/2010 00:00:00

My application dates are of the form

09/12/2009 00:00:00

which looks identical to me

My syntax is

=Count ({< [Application Date]= {$(='< v_LastAppYear')} >} Application)

which quite obviously doesn't work because (I'm guessing) I need to qualify the date. But with what? and how?

Thanks

George

Set analysis expression syntax

Anonymous — Fri, 27 May 2011 15:17:53 GMT

Hi Georgina,

The example below is used to get Year To Date Last Year.....

sum({<CAL_YEAR_WEEK=, MONTH=, QUARTER=, CAL_DATE = {"<=$(=date(max(addmonths(CAL_DATE,-12))))>=$(=date(max(yearstart(addmonths(CAL_DATE,-12)))))"}>} SALES)

Ensure that you have told the set expression to ignore selections in other date fields (, Year=, ) to ensure you get the correct syntax too.

Cheers.

Ad.

Re: Set analysis expression syntax

mike_garcia — Fri, 27 May 2011 15:20:33 GMT

Since the first Date Example you wrote is obviously in the format DD/MM/YYYY, I can assume the second one is also DD/MM/YYYY... I would just advise to verify if it is not MM/DD/YYYY.

After verifying that, you could try something like

=Count ({< [Application Date]= {"<='$(v_LastAppYear)'")} >} Application)

Double quotes indicate a search string, and in this case we are "searching" all the dates prior to the date in your variable ( "<=" ).

Mike.

Re: Set analysis expression syntax

mike_garcia — Fri, 27 May 2011 15:22:00 GMT

And another tip: head over to http://iqlik.wordpress.com to find a guide on the use of set analysis.

Set analysis expression syntax

danielrozental — Fri, 27 May 2011 15:23:03 GMT

Try it like this

=Count ({< [Application Date]= {"<=$(v_LastAppYear)"} >} Application)

or like this =Count ({< [Application Date]= {"<='$(v_LastAppYear)'"} >} Application)

Set analysis expression syntax

georgina_spary — Fri, 27 May 2011 16:09:17 GMT

Hurrah that first suggestion worked - thanks very much daniel. Also thanks to everyone else for some really good ideas which helped a lot