Set analysis expression syntax

[georgina_spary]

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

[Anonymous]

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.

[mike_garcia]

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.

[mike_garcia]

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

[danielrozental · Accepted solution]

Try it like this

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

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

[georgina_spary]

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