Set analysis - less than date minus 1 year

[canoebi99]

Hi All,

I am using a statement in my set analysis as follows:

Date={'<=$(=max(Date))'} and this works fine.

I now need to create another column for the previous yr and hence need a statement which gives me the max(Date) minus 1 year.

I have tried

Date={'<=$(=AddYears(max(Date),-1))'}

but can't seem to get this working? Can anyone point me in the right direction please?

Many thanks

Ben

[milkyryan]

Hi Ben,

Were you meaning to have one expression showing 12 months of sales based on a selection or just all loaded data?

Your current approach would show all sales occurring before the highest date value of loaded data, not necessarily the current year.

Following from this, if you wanted to show the previous year you can either use the InYearToDate function or a range (my preference) using concatenation in set analysis.

In my experience using a separate calendar as a data island has been very effective in providing MTD/YTD along with same period last year and so forth. What were you using as a calendar table?

In any case you could try a set expression similar to below:

Date = {"$(= '>=' & MonthStart(max(Date),-24) & '<=' & MonthEnd(max(Date),-12))"}

You may need to swap between >,>=, and -24 or -25 depending on how your dates are set up.

If you want to try scripting in a master calendar along with data island calendar I believe there are some other threads/blogs on here which cover the topic quite well.

[canoebi99]

Hi Ryan,

Many thanks for the response.

I am dealing with the years in the set analysis and I am setting these separately so that the columns are giving me the right years data.

The statement I posted above is to then let me get the data for those previous years only up to a certain date within that year.

Essentially I am trying to show comparisons for the same period in each column.

Hence, when the user selects a year, month and date range, then I am collecting the max date from this selection which gives me the date that I need to get all transactions up to for the YTD figure. Hence, my statement above works well for the current YTD based on the selections they have made.

What I need to achieve is to get the prev year and prior years columns to show their data for up to the same date but minus a year. (Hence, why I want to take a year of the max selected date).

Thanks

Ben

[milkyryan]

Hi Ben,

The problem you'll have (and depending on how your set expression works e.g. {$< vs {1<) is that when the user selects a year or month, the set won't return prior year data as it's filtered out via selections.

This was the main reason I used a data island (i.e. no joins to any other tables) calendar for the purposes of driving all date-driven expressions using set analysis where selecting one year still allows sums/accumulation over 1,2,3 or as many years prior to the selected year.

See below example table layout with calendar data island, selections would be made on this table then set analysis performed based on the calendar/dates in linked tables.

[canoebi99]

Thanks Ryan,

I am using the set analysis to clear any of the fields they might make a selection on so as to return the whole data for the prior year (up to the Date I need) so my whole statement would be something like this:

=Sum({$<FiscalYear={'$(vPrevFiscYear)'}, , FiscMonth =, Day= , Date={'<=#(=AddYears(max(Date),-1)'} >}((AgentGross) / 23) *20)

I was thinking that by clearing the fiscmonth and day selections and setting the fiscal year to the prev year I could then use the Date set analysis statement to report data up to the maximum selected date minus 1 year (Hence, giving me a comparison column for the prev yr for the same period)?

Cheers

Ben

[milkyryan]

Hi Ben,

You could try using sum({1< but I think you'll find the selections made in Date will still affect the set analysis.

This was the main reason I opt for using data island calendar as the set expressions are easier without having to remove selections on related fields.

It would be pretty quick and easy to test the data island approach, just do a resident load of your existing calendar and rename all fields with "Select" prefix, change selection boxes to use the data island calendar and try the new set expression.

Thanks.

[anbu1984]

Can you post sample app

[shawn-qv]

As you've stated, you want to ignore any date-specific filters which may contradict your set analysis, unless you're using the {1} set identifier. These are usually the Year, Month, Qtr, Week or Day filters.

Then something like this should work:

Prior Year = sum({<FiscYear=, FiscMonth=, Day=, Date = {"<=$(=addyears(max(Date), -1))"}>} (AgentGross/23)*20)

S.

[wonkymeister]

Brilliant Shawn, just what i was looking for thanks:

=addyears(max(Date), -1)

works a treat