topic Re: Expression return last 12 month sales in QlikView

Expression return last 12 month sales

Wed, 01 Jan 2014 07:07:09 GMT

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

jvishnuram — Wed, 01 Jan 2014 07:42:09 GMT

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

preminqlik — Wed, 01 Jan 2014 08:24:32 GMT

once try this

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

Re: Re: Expression return last 12 month sales

Wed, 01 Jan 2014 10:07:09 GMT

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

Colin-Albert — Wed, 01 Jan 2014 12:44:20 GMT

Try this

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

Re: Expression return last 12 month sales

nirav_bhimani — Wed, 01 Jan 2014 13:37:45 GMT

Hi,

try this expression.

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

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

distinct COMPANY)

Regards,

Nirav bhimani

Re: Expression return last 12 month sales

Wed, 01 Jan 2014 20:41:46 GMT

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