2 Replies Latest reply: Feb 14, 2014 2:33 AM by jagan mohan rao appala

# Compare this sum of sales in this month with the same month last year

I would like to compare the SALES_VALUE where the End_Date is selected from a list box (e.g. 31-12-2013) with the same month last year (e.g. 31-12-2012).  I tried using the following set analysis and it doesn't work.  Appreciate your time and effort to advise on how to make this work.

sum({\$<END_DATE={\$(=YEAR(END_DATE)-1 & '-' & =MONTH(END_DATE) & '-' & =DAY(END_DATE))}>} SALES_VALUE)

• ###### Re: Compare this sum of sales in this month with the same month last year

Please try this for Year to date

Sum({\$<D.Year={'\$(vLastYear)'},D.Month={"=\$(vMaxMonth)"},D.YearMonth=>}SALES_VALUE)

or this for single Month vs same Month previous year

Sum({\$<D.Year={'\$(vLastYear)'},D.Month={\$(=max(D.Month))},D.YearMonth=>}SALES_VALUE)

Where

vLastYear = Max(D.Year) - 1

vMaxMonth = right(Max(D.YearMonth),2)

D.YearMonth excluded as it is the selection

• ###### Re: Compare this sum of sales in this month with the same month last year

Hi,

Try this expressions

For selected month:

Sum({\$<YearDimensionName=, MonthDimensionName= QuarterDimensionName=, DateDimensionName={'>=\$(=MonthStart(Max(DateDimensionName)))<=\$(=MonthEnd(Max(DateDimensionName)))'}>}SALES_VALUE)

For previous year same month:

Sum({\$<YearDimensionName=, MonthDimensionName= QuarterDimensionName=, DateDimensionName={'>=\$(=MonthStart(Max(DateDimensionName), -12))<=\$(=MonthEnd(Max(DateDimensionName), -12))'}>}SALES_VALUE)

Replace YearDimensionName=, MonthDimensionName= QuarterDimensionName=,DateDimensionName  with your corresponding date dimensions.

Hope this helps you.

Regards,

Jagan.