Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
domibs
Contributor
Contributor

Dynamic result KPI selection

Hello, I've created the below KPI calculation to get the variation between the first price et the last price : 

((LastValue("Price"))-(FirstValue("Price")))/(FirstValue("Price"))

The calculation works but when I filter on month (field "[Year month]"),  the result of the KPI calculation does not change with the selection. 

For exemple : 

Reference Year month Price
123990 2020-10             2,85 €
123990 2020-12             2,85 €
123990 2021-04             2,95 €
123990 2021-10             3,25 €
123990 2022-03             3,35 €
123990 2022-04             3,70 €

 

The variation between the last price and the first price in this table is 29,82% ((3,70-2,85)/2,85).

If I filter only on few months, I get the below table. And the result of the KPI calculation should be : 13,85% ((3,70-3,25/3,25).

Reference Year month Price
123990 2021-10             3,25 €
123990 2022-03             3,35 €
123990 2022-04             3,70 €

 

However, the result does not change with the month selection in my Qlik application. 

Could you please help me to find the solution ? 

Thank you,

Have a great day,

Labels (3)
1 Reply
Angela_Zhou
Contributor III
Contributor III

How about,

(firstsortedvalue(Price, -[Year month]) - firstsortedvalue(Price, [Year month]))
/
firstsortedvalue(Price, [Year month])

Here, for any selected [Year month] with assumption of this field can be sorted in ASC order by year + month, then:

  • firstsortedvalue(Price, [Year month]) = the price of oldest year+month 
  • firstsortedvalue(Price, -[Year month]) = the price of most recent year+month (pay attention to the negative sign)


 

Angela Z.