Add Column with percentage change in Table Chart

[#1]

Hey,

I have the following problem.

I have a table with 3 columns in my QVD-file.

The colums are month_year, person, sales

month_year	person	sales
201408	A	55
201409	A	66
201408	B	100
201409	B	125
201408	C	5
201409	C	7

I want to solve the following problem:

I want to know how much more ore less a person sales, compared by the last month and the month before the last month, regarding the actual month (201410 in this case)

If I generate an expression as follows:

=(if(Month(month_year)=Month(Today())-1,sales))/(if(Month(month_year)=Month(Today())-2,sales))-1

with the dimension person I only get NULL-values.

Can you help me solving this case?

Thanks a lot in advance and best Regards!

Alexander

[alexandros17 · Accepted solution]

Create a chart using person as dimension and add an expression as:

Sum({$ <month_year = {'$(=Year(Today())*100+Month(Today())-1)'}>} sales)

/

Sum({$ <month_year = {'$(=Year(Today())*100+Month(Today())-2)'}>} sales)

[#3]

Thanks Alessandro, this works perfectly

[#4]

Hey, the expression works, when I am in year 2014 only.

When I got January in 2015 the expression won't work anymore when I try to see the change from 201411 to 201412. This also occurs when I want to analyse the change from 201412 to 201501 in February. My Data goes into this application every month and I do want an expression which works properly without changing it anymore.

Is there any solution to this problem?

[alexandros17]

A simple if:

month_year = {'$(=

if(month(Today())<=2, (Year(Today())-1)*100, Year(Today())*100) +

if(month(Today())=1, 11,if(month(Today())=2, 12, month(today())-1))

)'}

[#6]

Okay, that looks better.

Can you tell me, at which point of the expression I have to put this?

Should it look like this? And is it identical to write for the month: 1 or 01?

Sum({$ <month_year =

{'$(=

if(month(Today())<=2, (Year(Today())-1)*100, Year(Today())*100) +

if(month(Today())=1, 12,if(month(Today())=2, 1, month(today())-1))

)'}sales)

/

Sum({$ <month_year =

{'$(=

if(month(Today())<=2, (Year(Today())-1)*100, Year(Today())*100) +

if(month(Today())=1, 11,if(month(Today())=2, 12, month(today())-2))

)'}

sales)

[alexandros17]

1 is better for month, the expression should have to work

let me know

[#8]

unfortunately it is not working

I also tried it another way, but it is also not working. Is there anything wrong with the if-clauses?:

=Sum({$ <month_year =

{'$(=

if(Month(Today())=01,((Year(Today())-1)*100)+12,Year(Today())*100+Month(Today())-1)

)

'}sales)

/

Sum({$ <month_year =

{'$(=

if(Month(Today())=01,((Year(Today())-1)*100)+11,

if(Month(Today())=02,((Year(Today())-1)*100)+12,Year(Today())*100+Month(Today())-1)

)

)'}

sales)

[#9]

I found another way, it might not be that elegant, but it seems to work, can you check it, please?

=if(Month(Today())=1,

(Sum({$ <month_year = {'$(=(Year(Today())-1)*100+12)'}>} visits)

/Sum({$ <month_year = {'$(=(Year(Today())-1)*100+11)'}>} sales)) -1,

if(Month(Today())=2,

(Sum({$ <month_year = {'$(=Year(Today())*100+1)'}>} sales)

/Sum({$ <month_year = {'$(=(Year(Today())-1)*100+12)'}>} sales)) -1,

(Sum({$ <month_year = {'$(=Year(Today())*100+Month(Today())-1)'}>} sales)

/Sum({$ <month_year = {'$(=Year(Today())*100+Month(Today())-2)'}>} sales)) -1

)

)

[alexandros17]

Good it can work!