topic Re: How to find a Median over Months in App Development

How to find a Median over Months

Sam07 — Tue, 10 Dec 2024 15:06:51 GMT

Hello everyone,

I need to develop a metric that helps me finding the Median across months. For instance, in January, the metric has to return the median of January's values. But in February, the metric has to make a list of the values for January and February and bring the median accordingly the values of these two months. And so on for the other months. In March, the metric has to find the median according to the values of January, February and March.

This is the formula I'm currently using:

Median({<[Fiscal Date]={">=$(=YearStart(max([Fiscal Date])))"},VRMBDAFLG_LOT_APPROVED={'1'},VRMBDAFLG_OUTLIER={'1'}>}VRMBDADAYS_TO_BE_RELEASED)

My sincere thanks in advance

Re: How to find a Median over Months

Clement15 — Tue, 10 Dec 2024 15:12:38 GMT

Hello,

In what type of graph do you want to use this measure?

Re: How to find a Median over Months

Sam07 — Tue, 10 Dec 2024 15:21:04 GMT

@Clement15 thank you for willing to help me. In a line chart and a KPI. In addition, in the line chart I'm using months for the X axis.

Re: How to find a Median over Months

Clement15 — Tue, 10 Dec 2024 16:44:49 GMT

Hello, For the KPi I use this formula:

{<Date={">$(=YearStart(max(Date)))) <$(=max(Date))"},[YearMonth]=,[Year]=,Date=,Month=>}median(aggr(sum(Value),[YearMonth]))

It allows you to have the median as you want over the year, the set analysis allows you to make the right selection by just selecting the max date you want. Here, YearMonth is created using ' MonthName(Date) as YearMonth ' in the script.

For the graph, I used this in measure:

RangeFractile(0.5, above( sum(Value),0,12))

and YearMonth in dimension

The problem is that if you have several years, it will not be limited to the beginning of it.

These formulas are examples, you have to adapt them to your data

Re: How to find a Median over Months

Sam07 — Wed, 11 Dec 2024 09:40:04 GMT

Thank you for helping me, @Clement15 . I'm going to try again using the formula you shared with me.

I just would like to check with you this formula:

{<Date={">$(=YearStart(max(Date)))) <$(=max(Date))"},[YearMonth]=,[Year]=,Date=,Month=>}median(aggr(sum(Value),[YearMonth]))

It seems that the beginning of it is missing.

Re: How to find a Median over Months

Clement15 — Wed, 11 Dec 2024 09:46:48 GMT

What do you want to check? The formula is correct on my side. Just adapt it with your measure

Re: How to find a Median over Months

Sam07 — Wed, 11 Dec 2024 09:50:15 GMT

Okay, my mistake. Thank you.