Comparing YearMonth to Previous Year.

[#1]

Hi,

I've created a set analysis to display the current 'selected' YearMonth (Current dimension) this is a date format YYYY/MM within the current DB field:

The analysis to calculate where sales total > 0 = COUNT({$<SALES_TOTAL={">0"}>}  SALES_TOTAL)

This will show the selected 'YearMonth' and the sales total for that specific month with sales > 0.

The YearMonth looks like " 2016/07 " which is an existing field.

I need to create an expression where the sales total >0 (as shown above) with the YEARMONTH (currently selected) - 12 months?

So, I am having problems trying to compare in a KPI last years figure.

Many thanks.

[jonathandienst]

I am confused - there is no date selection in

COUNT({$<SALES_TOTAL={">0"}>}  SALES_TOTAL)

So it will include data for dates according to user selections. I think you may have copied the incorrect expression into your post.

[Anonymous]

Hi

Try this

Current month (last year)            

2015/07

       Count({$<[YearMonth]={"$(=Date(AddYears(Max(YearMonth), -1), 'YYYY/MM'))"}>} SALES_TOTAL)

Last Month Sales    

2016/06

  Count({$<[YearMonth]={"$(=Date(AddMonths(Max(YearMonth), -1), 'YYYY/MM'))"}>} SALES_TOTAL)

[#4]

Hi Sorry,

I didn't explain this too well. I already have a dimension which calculates the month today (YEARMONTH).

I need to add to the existing expression to include last years comparison.

[#5]

This doesn't seem to work

[Anonymous]

Hi

try the following...

Selecting  dates between 12 months and present month .....

Count({$<[YearMonth]={">=$(=Date(AddYears(Max(YearMonth), -1), 'YYYY/MM')<=$(=Date(max(YearMonth),'YYYY/MM'))"},SALES_TOTAL={">0"}>} SALES_TOTAL)

[sunny_talwar]

How about this:

This YearMonth

Count({$<YearMonth = {"$(='>=' & Date(MonthStart(Max({<SALES_TOTAL={">0"}>} YearMonth)), 'YYYY/MM') & '<=' & Date(MonthEnd(Max({<SALES_TOTAL={">0"}>} YearMonth)),'YYYY/MM'))"}>} SALES_TOTAL)

LastYear's YearMonth

Count({$<YearMonth = {"$(='>=' & Date(MonthStart(Max({<SALES_TOTAL={">0"}>} YearMonth), -12), 'YYYY/MM') & '<=' & Date(MonthEnd(Max({<SALES_TOTAL={">0"}>} YearMonth), -12),'YYYY/MM'))"}>} SALES_TOTAL)