Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try this
=count({$<year=, month=, date = {">=$(=MonthStart(vSelectedDate, -23))<=$(=MonthStart(vSelectedDate, -11))"}>} distinct COMPANY)/1
Hi Paul,
Is possible please post qvw it will gives the clear picture and hence we can make a play around.
once try this
=count({$<year=, month=, date = {">=$(=MonthStart(vSelectedDate, -12))<=$(=vSelectedDate)"}>} distinct COMPANY)
Try this
=count({$<year=, month=, date = {">=$(=MonthStart(vSelectedDate, -23))<=$(=MonthStart(vSelectedDate, -11))"}>} distinct COMPANY)/1
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
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