Calculating change between months in percentage

[#1]

Hi!

I've got a table with the following columns:

RecordID	OpenDate	OpenMonth	OpenYear
10	2015-01-02	jan	2015
12	2014-10-11	okt	2014
15	2015-09-14	sep	2015
27	2015-02-10	feb	2015
18	2015-02-15	feb	2015

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!

[MK_QSL · Accepted solution]

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)

[marcus_sommer]

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

[#4]

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.

[#5]

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