topic Re: median pivot in QlikView

median pivot

Mon, 12 Sep 2016 16:05:10 GMT

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

Re: median pivot

sunny_talwar — Mon, 12 Sep 2016 16:33:14 GMT

May be try this:

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

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

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

Re: median pivot

Tue, 13 Sep 2016 08:40:22 GMT

Thanks.

The second expression does what I was looking for.

Re: median pivot

Tue, 13 Sep 2016 09:15:50 GMT

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

Re: median pivot

sunny_talwar — Tue, 13 Sep 2016 15:40:04 GMT

Try this and see if this helps.

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

Re: median pivot

Tue, 13 Sep 2016 15:52:59 GMT

Thank you very much

Re: median pivot

Fri, 30 Sep 2016 14:11:57 GMT

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