Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

calculate the average of the first quartile

I want to calculate the average of the first quartile, and use it as a limit for a gauge.

All formula's work seperately but i cannot get the total to work.

This is my formula:

avg(if({<bouwmarkt=>}[Totaal Kosten] < fractile({<bouwmarkt=>}[Totaal Kosten],0.25),{<bouwmarkt=>}[Totaal Kosten]))


i can use

fractile({<bouwmarkt=>}[Totaal Kosten],0.25)

to create a reference line and that works fine


i can make an if statement that only shows value if selected bouwmarkt is below 1st quartile average and that works fine:

if([Totaal Kosten] < fractile({<bouwmarkt=>}[Totaal Kosten],0.25),[Totaal Kosten])



So i want to compare a "bouwmarkt" with the average of the 1st quartile.


Does anybody know what i'm doing wrong?

Labels (1)
4 Replies
sunny_talwar

What is your dimension on which you want to calculate the fractile?

Anonymous
Not applicable
Author

The dimension is "bouwmarkt"which means DIY store.

I want to compare the values of the selected bouwmarkt to the first quartile of all "bouwmarkt"

sunny_talwar

How about this?

Avg({<bouwmarkt=>}

     Aggr(

          If([Totaal Kosten] < Fractile({<bouwmarkt=>} [Totaal Kosten], 0.25), [Totaal Kosten]), bouwmarkt))

Anonymous
Not applicable
Author

Didn't get it to work with Aggr()

But i found another solution with the magical total() function. I dont know what it exactly does but it works!

Avg({<bouwmarkt>}

     total(

          if([Totaal kosten] <= fractile({<bouwmarkt=>} total([Totaal kosten]),0.25),[Totaal kosten])))