Get the value from previous month in a line chart

pedrohenriqueperna — Fri, 23 Aug 2024 14:21:58 GMT

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?

Re: Get the value from previous month in a line chart

rubenmarin — Sun, 25 Aug 2024 07:03:23 GMT

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

topic Get the value from previous month in a line chart in App Development

Get the value from previous month in a line chart

Re: Get the value from previous month in a line chart