Calculations on Aggregation

[wttaryde]

I have a calculation that by itself produces the correct number, but when in concert with other calculations, the result is wrong.

The basis of the calculation is:

(current year Fractile - 10-years ago Fractile) / 10-years ago Fractile

Qlik Sense calculates

• current year Fractile as 4.0
• 10-years ago Fractile as 2.2
• (this year - 10-year ) / 10-year as 78.6%
• Excel says this number should be 81.8%

I have experienced a similar problem where I needed the whole calculation to be within a single aggregation.  I just don't know how to write this so that I'm getting 2 fractiles for 2 different time periods.

Here's my expression:

(
Fractile({1<Year = {$(=Max(num#(Year ,'####')))}>} Aggr(
Sum({1<QPermID={"DF0650_R_1_*"}>}Response)/$(vTDSubsAssets)
,YearID,Year),0.5)

-

Fractile({1<Year = {$(=Max(num#(Year ,'####')-9))}>} Aggr(
Sum({1<QPermID={"DF0650_R_1_*"}>}Response)/$(vTDSubsAssets)
,YearID,Year),0.5)
)
/
Fractile({1<Year = {$(=Max(num#(Year ,'####')-9))}>} Aggr(
Sum({1<QPermID={"DF0650_R_1_*"}>}Response)/$(vTDSubsAssets)
,YearID,Year),0.5

By themselves, each piece calculations correctly, but the whole gives me a wrong number.

[rubenmarin · Accepted solution]

Hi, have you checked that is not a rounding issue?, maybe you are only seeing 2.2 because the number is formatted to 1 decimal but really it's something like 2.24, wich will return 78,6%

[wttaryde]

OMG.  Such a simple answer and absolutely correct.  I've seen this before, too, and forgot to check on this one.  I got caught up in the possibility of something bigger.