Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Not applicable

Calculating change between months in percentage

Hi!

I've got a table with the following columns:

RecordIDOpenDateOpenMonthOpenYear
102015-01-02jan2015
122014-10-11okt2014
152015-09-14sep2015
272015-02-10feb2015
182015-02-15feb2015

What I'm trying to accomplish is a text object in which the change between the number of record between the current selected month, and the month 3 months before is shown in percentage.

The formula for this is ((V1-V2)/V2)*100

V1= Current month

V2= Previous month

I can get the values for the current month and the past month using this_

Current month:

count({$<[OpenMonth]=, [OpenYear]=, OpenDate={">=$(=MonthStart(Max(OpentDate)))<$(=MonthEnd(Max(OpenDate)))"}>} DISTINCT RecordID)

Past month (3 months ago):

count({$<[OpenMonth]=, [OpenYear]=, OpenDate={">=$(=MonthStart(AddMonths(Max(OpenDate),-3)))<=$(=MonthEnd(AddMonths(Max(OpenDate),-3)))"}>} DISTINCT RecordID)

So the formula should be:

num(

sum(

count({$<[OpenMonth]=, [OpenYear]=, OpenDate={">=$(=MonthStart(Max(OpentDate)))<$(=MonthEnd(Max(OpenDate)))"}>} DISTINCT RecordID)

-

count({$<[OpenMonth]=, [OpenYear]=, OpenDate={">=$(=MonthStart(AddMonths(Max(OpenDate),-3)))<=$(=MonthEnd(AddMonths(Max(OpenDate),-3)))"}>} DISTINCT RecordID)

)

/

count({$<[OpenMonth]=, [OpenYear]=, OpenDate={">=$(=MonthStart(AddMonths(Max(OpenDate),-3)))<=$(=MonthEnd(AddMonths(Max(OpenDate),-3)))"}>} DISTINCT RecordID)

,'#,#%')

This gives an error message about nested expressions not being allowed, which I guess is because I have combined the max() function with the sum() function.

So I used the aggr() function around it, like this:

num(

sum(aggr(

count({$<[OpenMonth]=, [OpenYear]=, OpenDate={">=$(=MonthStart(Max(OpenDate)))<$(=MonthEnd(Max(OpenDate)))"}>} DISTINCT RecordID)

-

count({$<[OpenMonth]=, [OpenYear]=, OpenDate={">=$(=MonthStart(AddMonths(Max(OpenDate),-3)))<=$(=MonthEnd(AddMonths(Max(OpenDate),-3)))"}>} DISTINCT RecordID)

,OpenDate))

/

count({$<[OpenMonth]=, [OpenYear]=, OpenDate={">=$(=MonthStart(AddMonths(Max(OpenDate),-3)))<=$(=MonthEnd(AddMonths(Max(OpenShortDate),-3)))"}>} DISTINCT RecordID)

,'#,#%')

This gives me a result of 96,7%, when in reality it should be giving me 3,3%. My conclusion is that this is because Qlikview cannot find a value for the expression where I get the previous month's value and subtract it from the current month's.

IE, this expression gets a value of zero:

count({$<[OpenMonth]=, [OpenYear]=, OpenDate={">=$(=MonthStart(AddMonths(Max(OpenDate),-3)))<=$(=MonthEnd(AddMonths(Max(OpenDate),-3)))"}>} DISTINCT RecordID)

,OpenDate))

Why is this? I'm lost in all these aggregations!

1 Solution

Accepted Solutions
MVP
MVP

Re: Calculating change between months in percentage

Use below

=(SUM({<OpenYear = , OpenMonth = , OpenDate = {">=$(=Date(MonthStart(Max(OpenDate))))<=$(=Date(MonthEnd(Max(OpenDate))))"}>}RecordID)

-

SUM({<OpenYear = , OpenMonth = , OpenDate = {">=$(=Date(MonthStart(Max(OpenDate),-3)))<=$(=Date(MonthEnd(Max(OpenDate),-3)))"}>}RecordID))

/

SUM({<OpenYear = , OpenMonth = , OpenDate = {">=$(=Date(MonthStart(Max(OpenDate),-3)))<=$(=Date(MonthEnd(Max(OpenDate),-3)))"}>}RecordID)

You are getting - because value of 3 months ago is 0...

Change you date 2014-10-11 to 2015-10-11 and try to get the same result for previous month instead of 3 months ago.. you will get result...

=(SUM({<OpenYear = , OpenMonth = , OpenDate = {">=$(=Date(MonthStart(Max(OpenDate))))<=$(=Date(MonthEnd(Max(OpenDate))))"}>}RecordID)

-

SUM({<OpenYear = , OpenMonth = , OpenDate = {">=$(=Date(MonthStart(Max(OpenDate),-1)))<=$(=Date(MonthEnd(Max(OpenDate),-1)))"}>}RecordID))

/

SUM({<OpenYear = , OpenMonth = , OpenDate = {">=$(=Date(MonthStart(Max(OpenDate),-1)))<=$(=Date(MonthEnd(Max(OpenDate),-1)))"}>}RecordID)

View solution in original post

4 Replies
MVP
MVP

Re: Calculating change between months in percentage

Use below

=(SUM({<OpenYear = , OpenMonth = , OpenDate = {">=$(=Date(MonthStart(Max(OpenDate))))<=$(=Date(MonthEnd(Max(OpenDate))))"}>}RecordID)

-

SUM({<OpenYear = , OpenMonth = , OpenDate = {">=$(=Date(MonthStart(Max(OpenDate),-3)))<=$(=Date(MonthEnd(Max(OpenDate),-3)))"}>}RecordID))

/

SUM({<OpenYear = , OpenMonth = , OpenDate = {">=$(=Date(MonthStart(Max(OpenDate),-3)))<=$(=Date(MonthEnd(Max(OpenDate),-3)))"}>}RecordID)

You are getting - because value of 3 months ago is 0...

Change you date 2014-10-11 to 2015-10-11 and try to get the same result for previous month instead of 3 months ago.. you will get result...

=(SUM({<OpenYear = , OpenMonth = , OpenDate = {">=$(=Date(MonthStart(Max(OpenDate))))<=$(=Date(MonthEnd(Max(OpenDate))))"}>}RecordID)

-

SUM({<OpenYear = , OpenMonth = , OpenDate = {">=$(=Date(MonthStart(Max(OpenDate),-1)))<=$(=Date(MonthEnd(Max(OpenDate),-1)))"}>}RecordID))

/

SUM({<OpenYear = , OpenMonth = , OpenDate = {">=$(=Date(MonthStart(Max(OpenDate),-1)))<=$(=Date(MonthEnd(Max(OpenDate),-1)))"}>}RecordID)

View solution in original post

MVP & Luminary
MVP & Luminary

Re: Calculating change between months in percentage

If you used your approach ((V1-V2)/V2)*100 you don't need these sum() or aggr(sum()) around the counts and also your aggr-result looked quite correct. 96,7% from previous to current is the same like 3,3% difference between them - it's only a question from point of view.

- Marcus

Not applicable

Re: Calculating change between months in percentage

Hi, and thank you.

The 0 as a result isn't because of lack of data. My real application has 260000 records, which includes many entries for each date.

I tried your proposed solution, and it seems to work with some changes. I replaced sum() with count() and added distinct before the recordID. I also warpped the entire thing in num().

My final expression looks like this:

=num(

(count({<OpenYear = , OpenMonth = , OpenDate = {">=$(=Date(MonthStart(Max(OpenDate))))<=$(=Date(MonthEnd(Max(OpenDate))))"}>}DISTINCT RecordID)

-

count({<OpenYear = , OpenMonth = , OpenDate = {">=$(=Date(MonthStart(Max(OpenDate),-3)))<=$(=Date(MonthEnd(Max(OpenDate),-3)))"}>}DISTINCT RecordID))

/

count({<OpenYear = , OpenMonth = , OpenDate = {">=$(=Date(MonthStart(Max(OpenDate),-3)))<=$(=Date(MonthEnd(Max(OpenDate),-3)))"}>}DISTINCT RecordID)

,'#,#%')

There's a couple of things I don't really understand. The difference between my non-functioning exporession and my modified version of your suggestions seems to be the following:

- Not using the aggr() function

- Wrapping the Date() function around the MonthStart() and MonthEnd() functions

- Not using sum()

- Using the MonthStart() function to set the date range. I used the AddMonths() function instead. When reading the expression, I anticipated it to subtract 3 days instead of 3 months, but I guess that's not the case.

Thank you very much for your help, now it works and I just have to figure out why.

Not applicable

Re: Calculating change between months in percentage

Yes, it turns out I didn't need the aggr() function, see my reply above. I guess googleing the error message (nested aggregations not allowed something something) lead me astray, all the results referred to the aggr() function as a solution.

The result I was getting was correct in some sense, but only because my formula "worked" even when one of the values was unintentionally set to 0. It really was only calculating the number of records of the previous month and divided that by the number of records of the current month.

Basically, it was calculating

(V1-0/V2), which is the same as simply V1/V2. But not ((V1-V2)/V2).