topic Re: Selecting LAST MONTH in QlikView

Selecting LAST MONTH

Wed, 17 Jun 2015 08:36:14 GMT

i have some data in my Qlikview

My filters are Years and Months

2000 to 2015

and months Jan to December

Working Nicely: When i select year and Month together for example: year 2014 and month july it will give me the data of july 2014.

My Requirement if i click year 2014 or any previous years (without selecting month) it should give me the data of last month (December)

and If i Select 2015 it should give me the the data of Last Month ( in my case May is my last Month)

Please write any set analysis code for me plz

thanks

Re: Selecting LAST MONTH

qlikmsg4u — Wed, 17 Jun 2015 08:52:16 GMT

Try this

=Sum({$<Month={$(=Max(Month))}>}Sales)

Re: Selecting LAST MONTH

santhoo_san — Wed, 17 Jun 2015 09:35:22 GMT

Use below expression:

Sum({<Year={$(=Max(Year))}, Month= {"$(=max({<Year={$(=max(Year))}>} Month))"},Month= >}Sales)

Re: Selecting LAST MONTH

daniel_kusiak — Wed, 17 Jun 2015 10:50:13 GMT

Both will work fine but Sum({$<Month={$(=Max(Month))}>}Sales) is much easier 🙂

Re: Selecting LAST MONTH

Wed, 17 Jun 2015 12:19:31 GMT

thanks santhosh!

let me clear we have employees in our Department

year 2015

employees:

Year 2015 JAN FEB MAR APR MAY

No. of Employees (???) 285 290 295 287 291

WHEN I SELECT: YEAR 2015 AND MONTH JAN IT GIVES ME 285
BUT I NEED ... IF I SELECT ONLY YEAR 2015 ON MY YEAR LIST BOX

RESULT SHOULD BE TAKEN FROM LAST MONTH ( WHICH IS MAY) EMPLOYEES AT (???) IN MY TABLE

second: if i click year 2014 the result should be the number of employees in last month( December 2014)

MY EXPRESSION IS LIKE THIS

=num(Count(distinct{$<Year = {$(=Max(Year))}, Month= {"$(=max({<Year={$(=max(Year))}>} Month))"}, CAL_Month=,[Dept Code]= {'5555'}>} [Employeeid]))

Re: Selecting LAST MONTH

Wed, 17 Jun 2015 12:20:03 GMT

thanks but read my detailed reply in below

it is not working with me

Re: Selecting LAST MONTH

Wed, 17 Jun 2015 12:21:30 GMT

it is not working with me

i have written in above detail in my reply please review it

thanks

Re: Selecting LAST MONTH

santhoo_san — Wed, 17 Jun 2015 12:43:49 GMT

Hi Tahir,

Check the Month field. I presume that the Month field has string (ie., Apr, May ....), you can create another filed to identify the month as numeric (i.e, 1,2,3,4,5,6,7,8,9,10,11,12), as Max and min functions does not work on string. Please share a sample app, so that we can have a look.

Re: Selecting LAST MONTH

MayilVahanan — Wed, 17 Jun 2015 13:01:16 GMT

Hope you have date field,

=num(Count(distinct{<Date = {">=$(=MonthStart(Max(Date))) <= $(=MonthEnd(Max(Date)))"}, CAL_Month=,[Dept Code]= {'5555'}>} [Employeeid]))

Re: Selecting LAST MONTH

Wed, 17 Jun 2015 13:09:50 GMT

yes my month field is date field it will take the last day of the month

Re: Selecting LAST MONTH

Wed, 17 Jun 2015 13:46:26 GMT

i have a very heavy database behind it

and there are some official information

so .. sorry i can't send

Re: Selecting LAST MONTH

Chanty4u — Thu, 18 Jun 2015 03:37:09 GMT

try dis,

=Sum({$<Month={$(=Max(year)-1)}>}Sales)

hope dis helps

Thank you

Suresh

Re: Selecting LAST MONTH

Thu, 18 Jun 2015 08:22:44 GMT

thanks it works....