Please can someone tell me what is wrong with my YTD formula?:
SUM({$<Type = {'Wine'}, Date = {">=$(=YEARSTART(MIN(Date)))<=$(=MAX(Date))"} >} Sales )
Whenever I choose a week or month the YEARSTART is ignored and I get the number for sales during the selected period, not YTD.
I know =YEARSTART(MIN(Date)) works because I've tried it in a text box and it always returns 1st Jan of the selected/related year. But for some reason when I put it in a set, it doesn't.
You are using a $ set. This means that the month selection is applied and then the set expression. Hence current month only. Change the first $ sign to 1 to apply the set expression to the unfiltered data.
Perhaps the problem is that the Max function returns a number. You may need to convert it to a date:
<=$(=MAX(Date)) .. change to ... <=$(=Date(MAX(Date)))
Hope these help
Jonathan
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Jonathan's suggestion is a good one, but it might take you too far... using Set Identifier {1} instead of {$} causes ignoring all user selections. Perhaps you still need to respect their selections of other fields - Products, Regions, etc...
What you need to ensure is that any selection of Date-related fields (Year, Month, Quarter, Week) - are ignored. Add the following to your list of Set Modifiers:
Year=, Month=, Week=,
and all the selections of date-related fields will be ignored (if your fields are, indeed, named like these).
As a side comment - another, perhaps easier, way of validating YTD is using the function InYearToDate().
Thanks for the response. I've used the method of ignoring the other date dimensions before (as you suggest) and it works although it got very complicated when I tried doing a year offset e.g current YTD vs. previous YTD.
I've not used INYEARTODATE before, perhaps this will be the best solution. Can you assist me in creating the expression?
It's quite difficult to get it right "on dry", without the actual data... If you could post a sample app, I would try and build the expression for you. Otherwise, please refer to the Help Section.
