topic Re: Trying to compare with Previous Month in QlikView

Trying to compare with Previous Month

Wed, 18 Nov 2015 19:05:16 GMT

Hello all,

I have an expression set up that lets me average the amount of days it takes to complete a project. Within the dashboard I have a Multibox set up that allows the use to select the month they would like to view, which filters the below to only displayed completed stages within that period of time.

This is expressed as a bar graph, and ideally I would like to create a new expression that looks the previous month than what is currently selected.

=num(

if(IsNull(avg({$*<Projectstage={'Stage1'}, [Project Status]-={'PROJECT CANCELLED', 'ON HOLD'}, [Project.flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)),
0, avg({$*<Projectstage={'Stage1'}, [Project Status]-={'PROJECT CANCELLED', 'ON HOLD'}, [Project.flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)) +

if(IsNull(avg({$*<Projectstage={'Stage2'}, [Project Status]-={'PROJECT CANCELLED', 'ON HOLD'}, [Project.flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)),
0, avg({$*<Projectstage={'Stage2'}, [Project Status]-={'PROJECT CANCELLED', 'ON HOLD'}, [Project.flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)) +

if(IsNull(avg({$*<Projectstage={'Stage3'}, [Project Status]-={'PROJECT CANCELLED', 'ON HOLD'}, [Project.flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)),
0, avg({$*<Projectstage={'Stage3'}, [Project Status]-={'PROJECT CANCELLED', 'ON HOLD'}, [Project.flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)) +

if(IsNull(avg({$*<Projectstage={'Stage4'}, [Project Status]-={'PROJECT CANCELLED', 'ON HOLD'}, [Project.flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)),
0, avg({$*<Projectstage={'Stage4'}, [Project Status]-={'PROJECT CANCELLED', 'ON HOLD'}, [Project.flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime))
, '#,##0.0')

Re: Trying to compare with Previous Month

sinanozdemir — Wed, 18 Nov 2015 19:48:42 GMT

I guess in your set analysis you can include one more condition if you have a date dimension already. In the below expression, I am also assuming that you have months in your date dimension such as Aug, but formatted as date:

<[Date Dimension] = {$(=Date(AddMonths(Today(), -1), 'MMM'))}, ............>

Re: Trying to compare with Previous Month

Wed, 18 Nov 2015 20:02:43 GMT

Hi Sinan,

Thank you for your reply.

I've seen the above syntax applied elsewhere, but not certain how to layer into my definition.

You are correct in your assumption of my Date dimension, with one additional criteria being MMM YYYY. The dimension name is MonthName.

Re: Trying to compare with Previous Month

sinanozdemir — Wed, 18 Nov 2015 20:13:30 GMT

Let me give a try:

=num(

if(IsNull(avg({$*<MonthName = {$(=Date(AddMonths(Today(), -1), 'MMM YYYY'))}, Projectstage={'Stage1'}, [Project Status]-={'PROJECT CANCELLED', 'ON HOLD'}, [Project.flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)),
0, avg({$*<MonthName = {$(=Date(AddMonths(Today(), -1), 'MMM YYYY'))}, Projectstage={'Stage1'}, [Project Status]-={'PROJECT CANCELLED', 'ON HOLD'},[Project.flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)) +

if(IsNull(avg({$*<MonthName = {$(=Date(AddMonths(Today(), -1), 'MMM YYYY'))}, Projectstage={'Stage2'}, [Project Status]-={'PROJECT CANCELLED', 'ON HOLD'}, [Project.flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)),
0, avg({$*<MonthName = {$(=Date(AddMonths(Today(), -1), 'MMM YYYY'))}, Projectstage={'Stage2'}, [Project Status]-={'PROJECT CANCELLED', 'ON HOLD'},[Project.flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)) +

if(IsNull(avg({$*<MonthName = {$(=Date(AddMonths(Today(), -1), 'MMM YYYY'))}, Projectstage={'Stage3'}, [Project Status]-={'PROJECT CANCELLED', 'ON HOLD'}, [Project.flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)),
0, avg({$*<MonthName = {$(=Date(AddMonths(Today(), -1), 'MMM YYYY'))}, Projectstage={'Stage3'}, [Project Status]-={'PROJECT CANCELLED', 'ON HOLD'},[Project.flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)) +

if(IsNull(avg({$*<MonthName = {$(=Date(AddMonths(Today(), -1), 'MMM YYYY'))}, Projectstage={'Stage4'}, [Project Status]-={'PROJECT CANCELLED', 'ON HOLD'}, [Project.flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)),
0, avg({$*<MonthName = {$(=Date(AddMonths(Today(), -1), 'MMM YYYY'))}, Projectstage={'Stage4'}, [Project Status]-={'PROJECT CANCELLED', 'ON HOLD'},[Project.flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime))
, '#,##0.0')

Hope this works

Re: Trying to compare with Previous Month

Wed, 18 Nov 2015 20:41:03 GMT

Thanks for the help Sinan, unfortunately, it didn't produce a result.

No errors contained in the expression.

Re: Trying to compare with Previous Month

sinanozdemir — Wed, 18 Nov 2015 20:43:34 GMT

Is your MonthName field formatted as date or text? If the month and year names are aligned to the left, then they are text. if left, then they are dates. The above formula works only if your field is formatted as date.