Get the value from previous month in a line chart

[pedrohenriqueperna]

Hi,

I'm trying to build a line chart to display stock growth based on the previous month value, but I just can't get it to work.

In the axis I have month as dimension and I want to compare the month value to the previous month value. For instance, January would display the result for (January stock value / December stock value) - 1, while February would display (February stock value / January stock value) - 1.

I think I must use AddMonths. I guess it'd be something like this (except it's not working):

Num(
(
Sum({1<year=$:: year>} stock_qty)
/
Sum({1<year=$:: year, month = {"$(=Month(AddMonths(month, -1)))"}>} stock_qty)
)
- 1
, '+##.##0,0%;-##.##0,0%')

Any ideas?

[rubenmarin]

Hi, set analysis is calculated before the table, so it won't use a diffrent value for each row or bar.

You can use above, like:

Num(
(
Sum({1<year=$:: year>} stock_qty)
/
Above(Sum({1<year=$:: year>} stock_qty))
)
- 1
, '+##.##0,0%;-##.##0,0%')

But it won't work for the first value, so maybe you need another expression for the first value, checking when RowNo()=1 (The first chart value)

Num(If(RowNo()=1

 ,Sum({1<year=$:: year>} stock_qty)
  / Sum({1<year=$:: year, month = {"$(=Month(AddMonths(Min(month), -1)))"}>} stock_qty)

  ,Sum({1<year=$:: year>} stock_qty)
   / Above(Sum({1<year=$:: year>} stock_qty))

)- 1
, '+##.##0,0%;-##.##0,0%')

But probably it won't work because of the year, maybe you need a YearMonth field, so when you adds -1 month it knows it has to go the the previous year.

Another soution could be using the asof calendar https://community.qlik.com/t5/Design/The-As-Of-Table/ba-p/1466130