Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
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
Valued Contributor III

Re: Trying to compare with Previous Month

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

Re: Trying to compare with Previous Month

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
Valued Contributor III

Re: Trying to compare with Previous Month

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

Re: Trying to compare with Previous Month

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

No errors contained in the expression.

sinanozdemir
Valued Contributor III

Re: Trying to compare with Previous Month

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.