Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Trying to compare with Previous Month

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')

5 Replies
sinanozdemir
Specialist III
Specialist III

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'))}, ............>

Not applicable
Author

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.

sinanozdemir
Specialist III
Specialist III

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

Not applicable
Author

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

No errors contained in the expression.

sinanozdemir
Specialist III
Specialist III

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.