Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

1 Solution

Accepted Solutions

Re: Expression return last 12 month sales

Try this

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

6 Replies
jvishnuram
Contributor III

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.

preminqlik
Valued Contributor II

Re: Expression return last 12 month sales

once try this



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

Not applicable

Re: Re: Expression return last 12 month sales

Hi All

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

Dropbox - 000_QVD_v815 ask final.qvw

Re: Expression return last 12 month sales

Try this

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

nirav_bhimani
Contributor III

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

Not applicable

Re: Expression return last 12 month sales

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

Community Browser