topic Re: Last Two Months data on selection of Month in QlikView

Last Two Months data on selection of Month

khajafareed — Thu, 22 Feb 2018 09:27:26 GMT

Hi,

I'm facing issue while trying to show last two month results on selection of any month in Straight Table.

I have declared below variables:

VCurrentMonthM2:

=if((vMax2)=1,'Jan',

if((

vMax2)=2,'Feb',

if((

vMax2)=3,'Mar',

if((

vMax2)=4,'Apr',

if((

vMax2)=5,'May',

if((

vMax2)=6,'Jun',

if((

vMax2)=7,'Jul',

if((

vMax2)=8,'Aug',

if((

vMax2)=9,'Sep',

if((

vMax2)=10,'Oct',

if((

vMax2)=11,'Nov',

if((

vMax2)=12,'Dec',

''))))))))))))

VCurrentMonthM1:

=month(date(monthname(Date(if(isnull(GetFieldSelections(CommonMonth)),' ' ,max(CommonDate))))-1))

VMax2:

=if(isnull(GetFieldSelections(CommonMonth)),'',month(max(CommonDate))-2)

In Label i used: =vCurrentMonthM2&' ' &year&' '& 'Transactions Handled'

and Expression for label is:

Count({$<[Common Ticket Type]={'INCIDENT'},[Common U_Cancelled] ={'-1'},CommonYear=,CommonMonth={$(vCurrentMonthM2)},CommonDays=, [Common Priority]={'Priority-1'}, [Common Report_Customer]={'-1'},[Common Category_filter]={'-1'},[Common Summary_filter]={'-1'}, ResolvedTicket.isTicketOpen={'0'}>}ResolvedTicket.TicketID)

When i am selecting Dec Month i can see Previous 2 month Nov and Oct months Data.

Issue is when i am selecting Jan Month for year 2018 then i am able to see only Previous month Data i.e, Dec 2017 and not getting Nov Month's Data.

Re: Last Two Months data on selection of Month

YoussefBelloum — Thu, 22 Feb 2018 09:47:13 GMT

Hi,

did you created all this to be able to get back two months ? I Mean all this approach is created specifically only to get back two months ?

Because you have Addmonths() function which is easier in this case.

Re: Last Two Months data on selection of Month

khajafareed — Thu, 22 Feb 2018 10:08:45 GMT

Yeah, I need to get back two months based on current selection of Month.

Re: Last Two Months data on selection of Month

YoussefBelloum — Thu, 22 Feb 2018 10:23:19 GMT

ok so you can simply do this:

1. If don't already have Master Calendar with MonthYear field, Create a MonthYear field (MMYYYY format)

2. use this set analysis part (to replace it on your expression)

{< MonthYear={">=$(=Date(AddMonths(Date#(MonthYear,'MMYYYY'),-2),'MMYYYY'))<=$(=Date(Date#(MonthYear,'MMYYYY'),'MMYYYY'))" }>}

you will have this:

Count({$<[Common Ticket Type]={'INCIDENT'},[Common U_Cancelled] ={'-1'},CommonYear=,MonthYear={< MonthYear={">=$(=Date(AddMonths(Date#(MonthYear,'MMYYYY'),-2),'MMYYYY'))<=$(=Date(Date#(MonthYear,'MMYYYY'),'MMYYYY'))" }>} ,CommonDays=, [Common Priority]={'Priority-1'}, [Common Report_Customer]={'-1'},[Common Category_filter]={'-1'},[Common Summary_filter]={'-1'}, ResolvedTicket.isTicketOpen={'0'}>}ResolvedTicket.TicketID)