6 Replies Latest reply: Sep 19, 2016 8:21 PM by Sunny Talwar

# Like For Like Set Analysis

Hi All,

I trying to do some like for like analysis looking at the previous month and last year and I'm stumped on the syntax.  The data looks something like this:

Sep-2015Aug-2015Sep-20142192
Oct-2015Sep-2015Oct-20142211
Nov-2015Oct-2015Nov-20142207
Dec-2015Nov-2015Dec-20142196
Jan-2016Dec-2015Jan-20152219
Feb-2016Jan-2016Feb-20152224
Mar-2016Feb-2016Mar-20152185
Apr-2016Mar-2016Apr-20152172
May-2016Apr-2016May-20152173
Jun-2016May-2016Jun-20152146
Jul-2016Jun-2016Jul-20152129
Aug-2016Jul-2016Aug-20152109
Sep-2016Aug-2016Sep-20152093

The current month is provided as a selection criteria ultimately resulting in the straight table containing one row of data.

To get last month and Last Years headcount this syntax seems to work as long as none of the dimensions in the straight table are dates:

I'm struggling to find the right syntax to do a between statement in the set analysis that refers to LastYear and MonthYear  for the final column.

Any help is much appreciated

• ###### Re: Like For Like Set Analysis

May be try this:

Last Month

Last Year

• ###### Re: Like For Like Set Analysis

Hi Sunny, thanks for the response.  I've had a play with the above function and looked at the documentation.   It certainly works until I make a selection criteria of a specific month at which point the function returns no results.

Before Selection criteria:

 MonthYear Sum(Head_Flag) above(Sum(Head_Flag)) Above(Sum(Head_Flag), 12) Sep-2015 2192 - - Oct-2015 2211 2192 - Nov-2015 2207 2211 - Dec-2015 2196 2207 - Jan-2016 2219 2196 - Feb-2016 2224 2219 - Mar-2016 2185 2224 - Apr-2016 2172 2185 - May-2016 2173 2172 - Jun-2016 2146 2173 - Jul-2016 2129 2146 - Aug-2016 2109 2129 - Sep-2016 2093 2109 2192 Oct-2016 2091 2093 2211

Once selection criteria is set:

I was able to get a cumulative total using RangeAvg(Above(Sum(Head_Flag),0,12)) but again this only works if no selection criteria are made.

Thanks again for the assistance

• ###### Re: Like For Like Set Analysis

Try this:

1 month above

Above(Sum({<MonthYear, Month, Date, Year>}Head_Flag)) * Avg(1)

12 months above

Above(Sum({<MonthYear, Month, Date, Year>}Head_Flag), 12) * Avg(1)

12 month rolling average

RangeAvg(Above(Sum({<MonthYear, Month, Date, Year>}Head_Flag), 0, 12)) * Avg(1)

• ###### Re: Like For Like Set Analysis

Hi Sunny,

Thanks for all the assistance.  I continued reading up on the Above function after your last suggestion and ended up with the following:

Looks pretty similar to your suggestion above.  Can I ask what is the *Avg(1) doing in your example above?

I'm also trying to limit the results to the current month (without the user specifying it via a selection criteria.  I've settled on this code, but it has some issues if the user does actually use a selection criteria you get all the months up to the maximum from the set analysis:

Thanks Again!

• ###### Re: Like For Like Set Analysis

May be try this:

Only({<MonthYear = {">=\$(=Min(MonthYear))<=\$(=Max(MonthYear))"}>} Aggr(RangeAvg(Above(Sum({\$<MonthYear>} Head_Flag),0,12)), MonthYear))

or this:

Only({<MonthYear = {"\$(=Max(MonthYear))"}>} Aggr(RangeAvg(Above(Sum({\$<MonthYear>} Head_Flag),0,12)), MonthYear))

• ###### Re: Like For Like Set Analysis

Attaching a sample for you to have a look at. I am only ignoring selection in MonthYear field because I don't have other date and time fields, but you need to ignore all date and time related fields where you can possibly make selections and you don't want them to impact the result of your calculation.