Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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
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.