Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try it like this
=Count ({< [Application Date]= {"<=$(v_LastAppYear)"} >} Application)
or like this =Count ({< [Application Date]= {"<='$(v_LastAppYear)'"} >} Application)
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.
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.
And another tip: head over to http://iqlik.wordpress.com to find a guide on the use of set analysis.
Try it like this
=Count ({< [Application Date]= {"<=$(v_LastAppYear)"} >} Application)
or like this =Count ({< [Application Date]= {"<='$(v_LastAppYear)'"} >} Application)
Hurrah that first suggestion worked - thanks very much daniel. Also thanks to everyone else for some really good ideas which helped a lot