Calculating Median %

[gniady]

Hi All,

I'm struggling with calculating the median percentage in a Qlik Sense table.

My table includes one dimension: Code, which displays individual codes from DV01 to DV08 and PFTQ.

I successfully calculate the median count for last year using the following expression (which gives correct results):

Median(
Aggr(
Sum(
{1<
Monat = {"$(vD_Year_PY)"},
Code1 =
>}
CodeCount
),
Monat,Code1
))

However, when I try to calculate the median percentage, I only get the value for PFTQ—not for the other codes. Here's the set analysis I'm using:

median(aggr(sum(

{<

        Monat = {"$(vD_Year_PY)"},

        Code1 =

        >}

CodeCount)

/

sum(aggr(sum(

{1<

        Monat = {"$(vD_Year_PY)"},

        Code1 =

        >}

CodeCount), Monat)), Monat, Code1))

If the data I've provide is not sufficient, ping me.

[M_B]

Can you try with "total"?

sum(aggr(sum(total
{1<
Monat = {"$(vD_Year_PY)"},
Code1 =
>}
CodeCount), Monat)), Monat, Code1))

or

sum(total aggr(sum(.....

[gniady]

Hi M_B,

Thanks for prompt reply, unfortunately Totals do not work here, they take the total number of deviations. I'm attaching the results. The part with 93.1% for PFTQ is almost correct, it's only missing Median % for individual Codes and I am wondering how come it calculates correctly PFTQ Code but shows null for the rest.

[M_B]

The only thing I can see is that the Aggr() aren't matching. Try to treat the values you want to divide as 2 separate values.

median(aggr(sum( //this sum is not aggregating to Code1 so there is only 1 value and the rest will show null
{<
Monat = {"$(vD_Year_PY)"},
Code1 =
>}
CodeCount)

/

sum(aggr(sum(
{1<
Monat = {"$(vD_Year_PY)"},
Code1 =
>}
CodeCount), Monat)), Monat, Code1))

--------------------------------------------------------------------------------------------------

Try this to see if you get the rest of the values:

median(aggr(sum(
{<
Monat = {"$(vD_Year_PY)"},
Code1 =
>}
CodeCount), Monat, Code1))

/

sum(aggr(sum(
{1<
Monat = {"$(vD_Year_PY)"},
Code1 =
>}
CodeCount), Monat, Code1))

I am still not sure what the exact outcome you're looking for.

[gniady]

Hi M_B, 

Yes, I guess I'd need to describe it in more detail. Let's take an example for the sake of Median calculation with even data I took only first four months of previous year (create a variable from 1.01.2024 to 30.04.2024). I've attached the screen and explained how each element is calculated. I hope now all will be crystal clear. 🙂

[gniady · Accepted solution]

Hi All,

I've found the answer to my question, <Monat> after total function solves the issue of calculating totals for individual months and not whole year:

Median(

        Aggr(

            sum(

{<

        Monat = {"$(vD_Year_PY)"},

        Code1 =

        >}

CodeCount)

/

sum(total <Monat>

{<

        Monat = {"$(vD_Year_PY)"},

        Code1 =

        >}

CodeCount),

            Monat,Code1

        ))