Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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)
Thanks Alessandro, this works perfectly
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?
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))
)'}
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)
1 is better for month, the expression should have to work
let me know
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)
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
)
)
Good it can work!