Average of the fourth quartil PER PERIOD

[kennethand]

I need to measure: Average of fourth quartile per period

Step 1: Calculate the quartile point in a variable called ’vFraq_Ejerlejlighed’ using this expression:

=Fractile({ <[q19]={"Ejerlejlighed"}>} SøgeKvmEjer, 0.75)

Step 2: Calculate the the average of the fourth quartile:

SUM(     {$<[q19]={'Ejerlejlighed'},[SøgeKvmEjer] = {">=$(=vFraq_Ejerlejlighed)"}>} ([SøgeKvmEjer] * weight))

/

SUM(   {$<[q19]={'Ejerlejlighed'},[SøgeKvmEjer] = {">=$(=vFraq_Ejerlejlighed)"}>} (weight))

Then I like to show the result per period like this:

But the results are not right. I’m quite sure its because the variable ’vFraq_Ejerlejlighed’  to determin the fourth quartile point is calculated as a total (every entry in the data set) and not per period.

Any suggestions to solve this problem ?

[Vegar]

If you want to calculate something over all dimensions you could use the TOTAL parameter in fractile calculation.

Try. 

=Fractile(TOTAL { <[q19]={"Ejerlejlighed"}>} SøgeKvmEjer, 0.75)

https://help.qlik.com/en-US/qlikview/November2018/Subsystems/Client/Content/QV_QlikView/ChartFunctio...

[kennethand]

Thanks for the reply,

Unfortunaley it doesn't help me.

If I add Period to the variable expression like this:

=Fractile( { <[q19]={"Ejerlejlighed"},  [Periode]={"2018K2"]>} SøgeKvmEjer, 0.75)

It gives me the correct result but only for 2018K2.

So I need calculate the variable PER PERIOD and not per total

[Vegar]

You are right, I missunderstood your question.

$ expansions are calculated "before" the calculations per dimensions. So, as you suggest, the fractile calculation is calculated based on all periods in your selections.

You might need to rethink your logic.

Something like this could work. (It forces your fractile to be calculated per dimension)

IF(Fractile({<[q19]={"Ejerlejlighed"}>} SøgeKvmEjer, 0.75) > SøgeKvmEjer,
   SUM({$<[q19]={'Ejerlejlighed'}>} ([SøgeKvmEjer] * weight))
   /
   SUM( {$<[q19]={'Ejerlejlighed'}>} (weight)),
)

[BeeGees]

Avg(

Aggr(

Fractile({ <[q19]={"Ejerlejlighed"}>} SøgeKvmEjer, 0.75),

Periode)

) 

is a way to go

[kennethand]

Hi Vegar,

Thanks for your solution. I think we're pretty close but I'm not getting any numbers. The condition part doesn't trigger the calculation. 

It seems strange to me. Does anybody know why ?

[Vegar]

It might be the IF that is causing you trouble.

What happens if you add SøgeKvmEjer as an dimension to your object? I guess you will se some results. If you do then you might need to aggregate the nominator and the denominator per SøgeKvmEjer before the final division calculation.

[Vegar]

This might also be an possible solution.

   SUM({$<[q19]={'Ejerlejlighed'}>} ([SøgeKvmEjer] * weight * (Fractile({<[q19]={"Ejerlejlighed"}>} SøgeKvmEjer, 0.75) > SøgeKvmEjer) ))
   /
   SUM( {$<[q19]={'Ejerlejlighed'}>} (weight) * (Fractile({<[q19]={"Ejerlejlighed"}>} SøgeKvmEjer, 0.75) > SøgeKvmEjer))

Edit: removed the IF statement

[kennethand]

I'm not sure how to deal with this but I got this error message ?

[Vegar]

Skip that path to solution.
You may not have aggregations inside another aggregation.