Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
jopdaalmans
New Contributor

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?

4 Replies

Re: calculate the average of the first quartile

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

jopdaalmans
New Contributor

Re: calculate the average of the first quartile

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"

Re: calculate the average of the first quartile

How about this?

Avg({<bouwmarkt=>}

     Aggr(

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

jopdaalmans
New Contributor

Re: calculate the average of the first quartile

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])))