Expression return last 12 month sales

[#1]

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

[jvishnuram]

Hi Paul,

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

[preminqlik]

once try this

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

[#4]

Hi All

Thank you for all your help , below is my QV doc :-

Dropbox - 000_QVD_v815 ask final.qvw

[Colin-Albert · Accepted solution]

Try this

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

[nirav_bhimani]

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

[#7]

Hi All,

Thank you for all your reply and sharing.

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' )  &'-'

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

it will display Dec13-Jan13

For below expression NOT work fine :-

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

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

it will display Dec12-Jan12

Edit , i found the issue :-

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

&date(addmonths(max(YearMonth), -23), '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