6 Replies Latest reply: Jan 4, 2014 3:11 PM by Paul Yeo

# Expression return last 12 month sales

Hi All

I have a expression which is working fine  :-

=count({\$<year=, month=, date = {">=\$(=MonthStart(vSelectedDate, -11))<=\$(=vSelectedDate)"}>} distinct COMPANY)/1

This expression will return how many company base on the month and year i click for example 2013 Dec , it will return 100 company.

Now my question is , how can i modify the expression , so that same expression it will display for me number of company for Dec 2012 ?

I Try :-

=count({\$<year=, month=, date = {">=\$(=MonthStart(vSelectedDate, -23))<=\$(=vSelectedDate)"}>} distinct COMPANY)/1

Because 11+12=23

But it does not work.

if i use the expression :-

=count({\$<year=, month=, date = {">=\$(=MonthStart(vSelectedDate, -11))<=\$(=vSelectedDate)"}>} distinct COMPANY)/1

when i select Dec 2013 , it will display 100 company.

when i select Dec 2012 , it will display 150 company.

which is correct. But i want to view both 2012 and 2013 result display on 1 table.

Paul

• ###### Re: Expression return last 12 month sales

Hi Paul,

Is possible please post qvw it will gives the clear picture and hence we can make a play around.

• ###### Re: Expression return last 12 month sales

once try this

=count({\$<year=, month=, date = {">=\$(=MonthStart(vSelectedDate, -12))<=\$(=vSelectedDate)"}>} distinct COMPANY)

• ###### Re: Expression return last 12 month sales

Try this

=count({\$<year=, month=, date = {">=\$(=MonthStart(vSelectedDate, -23))<=\$(=MonthStart(vSelectedDate, -11))"}>} distinct COMPANY)/1

• ###### Re: Expression return last 12 month sales

Hi All,

There are many approach , I choose Colin approach because .

He modify from my original code. he replace move 11 to 23 and you change 0 to 11.

My next question is on the label display  :-

For below expression work fine :-

=' ' & date(max(YearMonth), 'MMM-YY' )  &'-'

it will display Dec13-Jan13

For below expression NOT work fine :-

=' ' & date(max(YearMonth)-11, 'MMM-YY' )  &'-'

it will display Dec12-Jan12

Edit , i found the issue :-

=' ' & date(addmonths(max(YearMonth),-12), 'MMM-YY' )  &'-'

Many time when i try to figure out why my script not work , and i manage to solve the problem.

So case close for this post.

Paul

• ###### Re: Expression return last 12 month sales

Hi,

try this expression.

=Count({<CalYear=, CalMonthYear=, CalMonth=, CalQuarter=, CalDate={'>=\$(=MonthStart(Max(CalDate), -12))<=\$(=MonthEnd(Max(CalDate), -12))'}>} distinct COMPANY)

Or

= Count ({\$<CalMonthYear=, CalYear={\$(=max(CalYear)-1)},CalMonthNumber={\$(=max(CalMonthNumber))}>}

distinct COMPANY)

Regards,

Nirav bhimani