Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
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)
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
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)), )
Avg( Aggr( Fractile({ <[q19]={"Ejerlejlighed"}>} SøgeKvmEjer, 0.75), Periode) )
is a way to go
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 ?
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
I'm not sure how to deal with this but I got this error message ?