topic Re: Percentage difference of 2 expressions in QlikView

Percentage difference of 2 expressions

davyqliks — Sat, 16 Nov 2024 01:43:59 GMT

Hi All,

I have a requirement to find the difference between 2 figures realised by expression:

I have tried Expression 1 - Expression 2 / Expression 2 but this does not give the same value as when i perform the same with the figures.

Here is my effort:

num (Sum({<[Month-Year] = {"$(=Date(Max({<HKD_Price *= {[*]}>}[Month-Year]), 'MMM-YYYY'))"}>} HKD_Price) -

Sum({<[Month-Year] = {"$(=Date(Max({<HKD_Price *= {[*]}>}[Month-Year]-1), 'MMM-YYYY'))"}>} HKD_Price) /

Sum({<[Month-Year] = {"$(=Date(Max({<HKD_Price *= {[*]}>}[Month-Year]-1), 'MMM-YYYY'))"}>} HKD_Price)

, '#,##0%')

Any advise on how i would achive the percentage difference between 2 sums would be much appreciated.

Thank you in advance.

Re: Percentage difference of 2 expressions

sunny_talwar — Tue, 10 Dec 2019 16:58:45 GMT

How about this

Num( Sum({<[Month-Year] = {"$(=Date(Max({<HKD_Price *= {[*]}>}[Month-Year]), 'MMM-YYYY'))"}>} HKD_Price) / Sum({<[Month-Year] = {"$(=Date(Max({<HKD_Price *= {[*]}>}[Month-Year]-1), 'MMM-YYYY'))"}>} HKD_Price) - 1 , '#,##0%')

Re: Percentage difference of 2 expressions

davyqliks — Wed, 11 Dec 2019 11:26:42 GMT

Thank you for the reply Sunny,

I will try to implement this shortly and follow up.

Much appreciated

Daniel

Re: Percentage difference of 2 expressions

davyqliks — Wed, 11 Dec 2019 13:35:07 GMT

Thank you Sunny (Again),

Your assistance is so much appreciated.

Thank you

Daniel

Re: Percentage difference of 2 expressions

davyqliks — Fri, 10 Jan 2020 13:23:51 GMT

Hi Sunny,

I have found an issue with results of the above.

I have decided to simplify things a little and need to find the % different between 2 sums

sum({<[Year]={'2019'}>} [HKD_Price])

and

sum({<[Year]={'2018'}>} [HKD_Price])

The figures are as follows and so know the difference must like below

2760530389.02

1504520710.05

58.8978% difference

Please can you assist in the expression to reach this result?

Thank you in advance

Daniel