topic Re: Median Expression in QlikView

Median Expression

gouthamkk — Mon, 25 Jul 2016 03:42:38 GMT

Hi Experts,

I am trying to write an Expression in a chart object to calculate the median of weight.

I have date, empid, branch,flag and position. And i have a history of 3 years data.

i would like to calculate the median of weight for past 24 months, excluding current month. I tried below expression

Median({<empid=,branch=, flag =, position=,>} aggr( weight,branch,flag, position ))

When no selection made i am getting correct median. But if i select an empid, the median is changing also i have to add date selections.

Can some please Advise.

Re: Median Expression

marcus_sommer — Mon, 25 Jul 2016 05:17:40 GMT

The expression had not a valid syntax - it should be look more like:

median(aggr(sum(Value), YourDimensions))

if you really needed an aggr-function and if you will need to consider if possible set analysis conditions belong to inner or outer aggregation.

AGGR...

When should the Aggr() function NOT be used?

Pitfalls of the Aggr function

- Marcus

Re: Median Expression

Gysbert_Wassenaar — Mon, 25 Jul 2016 06:41:18 GMT

You use the implicit aggregation function Only() when you don't specify on explicitly. And you need to add the set modifier to that aggregation too. So you must make the aggregation explicit:"

Median({<empid=,branch=, flag =, position=>} aggr( only({<empid=,branch=, flag =, position=>}weight) ,branch,flag, position ))

You may want to use sum() instead of only() if there are several weight values per combination of empid, branch, flag and position values.

Re: Median Expression

gouthamkk — Mon, 25 Jul 2016 11:25:18 GMT

HI,

this is helpfull. Is there any way i could also add date dimesion so that it caluclates only for last 24 month excluding current month

Re: Median Expression

Gysbert_Wassenaar — Mon, 25 Jul 2016 11:39:02 GMT

Yes, that's possible. Something like:

({<empid=,branch=, flag =, position=, date={"=date=>$(=monthstart(max(date),-24)) and date<$(=monthstart(max(date)))"}>}