median pivot

[#1]

Hello,

I would like to calculate the median value of the field "fatturato" (the expression in the pivot).

The pivot table has "carrier name" and "year" as dimensions like in the picture below.

I tried using the expression median(aggr(sum(sales),[Carrier Name]))

or median(aggr(sum(sales),[Carrier Name], Year))

or median(aggr(sum(sales),Year))     

but it doesn't work.

Can you help me please?

Thanks

[sunny_talwar · Accepted solution]

May be try this:

Median(TOTAL Aggr(Sum(sales), [Carrier Name], Year))

or

Median(TOTAL <Year> Aggr(Sum(sales), [Carrier Name], Year))

or

Median(TOTAL <[Carrier Name]> Aggr(Sum(sales), [Carrier Name], Year))

[#3]

Thanks.

The second expression does what I was looking for.

[#4]

The expression works with the sales but I have to use it also with another value calculated with the before() function:

sum(sales)- before(sum(sales))

which gives me the difference between the sales of one year and the sales of the year before.

when I try to calculate the median value of that doesn't work.

Do you know what is the problem? (I think it's beacause the before() function uses the columns to do the calculation).

Or there is anathor way to calculate the difference between years?

Thank you very much

[sunny_talwar]

Try this and see if this helps.

Median(TOTAL <Year> Aggr(Sum(sales) - Above(Sum(sales), [Carrier Name], Year))

[#6]

Thank you very much

[#7]

I've checked the data again and i noticed that some rows aren't calculated.

I've put the expression with the "above" function - aggr(sum(Sales)-above(sum(Sales)),[Carrier Name],Year) - in comparison to the expression with the "before" function - (sum(Sales)- before(sum(Sales)) -.

So the median value is also wrong.

In the picture below you can see some examples:

Do you know what is the problem?

Thanks