topic Re: finding percentage % difference between 2 sums in QlikView

finding percentage % difference between 2 sums

davyqliks — Sat, 16 Nov 2024 01:32:25 GMT

I need to find the % different between 2 sums, i have tried a few things but never get the calculated result below.

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

Re: finding percentage % difference between 2 sums

Taoufiq_Zarra — Fri, 10 Jan 2020 14:54:02 GMT

58.8978% is correct ?

and only from

2760530389.02

1504520710.05

Re: finding percentage % difference between 2 sums

Kushal_Chawda — Fri, 10 Jan 2020 15:56:14 GMT

may be try this

=(sum({<[Year]={'2019'}>} [HKD_Price]) - sum({<[Year]={'2018'}>} [HKD_Price]) )/sum({<[Year]={'2018'}>} [HKD_Price]) or (1- sum({<[Year]={'2019'}>} [HKD_Price]) / sum({<[Year]={'2018'}>} [HKD_Price]))*-1

Re: finding percentage % difference between 2 sums

davyqliks — Fri, 10 Jan 2020 16:22:00 GMT

Hi,

The figure is correct when i work outside of QV but i cannot make an expression to obtain the correct Value, thank you for the reply, if you have the solution please help.

Thank you in advance

Daniel

Re: finding percentage % difference between 2 sums

Kushal_Chawda — Fri, 10 Jan 2020 16:31:08 GMT

Looking at the values we won't be able to know what is the logic to get the %. A general % difference formula is already suggested. Please provide some sample data with logic to calculate % difference

Re: finding percentage % difference between 2 sums

davyqliks — Fri, 10 Jan 2020 16:58:09 GMT

HI,

Thank you for this, not sure i worded right but once i made the figures smaller this made sense.

Thank you

Re: finding percentage % difference between 2 sums

alexblagoveshchenskiy — Fri, 02 Apr 2021 17:13:03 GMT

Dear Kush,

Could you please correct me if i'm wrong.

But is there are any other option to calculate it?
My aggregation still incorrect.

Formula:

NUM(
(
(
sum({<[line_sku] = {$(=chr(34) & concat(distinct [temp_all_FF_struc.line], chr(34)&','&chr(34)) & chr(34))},d_type = {'target'}, yr_q = {'$(vYr)_$(vQ)'}>}pcs * $(vPLGF))
-
sum({<[line_sku] = {$(=chr(34) & concat(distinct [temp_all_FF_struc.line], chr(34)&','&chr(34)) & chr(34))},d_type = {'fact'}, yr_q = {'$(vYr_p)_$(vQ)'}>}pcs * $(vPLGF))
)
/ sum({<[line_sku] = {$(=chr(34) & concat(distinct [temp_all_FF_struc.line], chr(34)&','&chr(34)) & chr(34))},d_type = {'target'}, yr_q = {'$(vYr)_$(vQ)'}>}pcs * $(vPLGF))
)
, '#0%')

Thank you in advance!