Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Median based on Expression

Hi,

   We have to calculate median based on expression. we have age values and we calculate vehicle distribution for each vehicle using the expression; sum(base wt)/ sum(toatl <vehicle> base wt). now we need to find the median age value. that is age value at which vehicle distribution crosses 50%. Any help regarding this is appreciated.

3 Replies
MVP
MVP

Median based on Expression

You can probably work out an advanced aggregation expression, what is the context (dimensions) of your above expression? Could you post some sample data lines?

An advanced aggregation could look like

=median( aggr( sum([base wt])/sum(total<vehicle> [base wt]), vehicle))

Not applicable

Median based on Expression

We have age as dimension. we need to calculate median age.

sample data:

Age    Distibution

10          5%

20          10%

30          30%

40          35%

50          15%

60            5%

In this case, the median should be where the cumulative distribution crosses 50%.Here the median age will be 40.

We need a single median age for every vehicle

MVP
MVP

Re: Median based on Expression

You could try something like

=min(aggr( if(rangesum(above(DistributionExpression,0,rowno()))>=50, Age),Age))

where DistributionExpression could be your above expression.

The load order of age must be sorted in ascending order to make this work, so maybe load your sorted age table first in script.

See also attached.

Community Browser