Median Expression

[gouthamkk]

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. 

[marcus_sommer]

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

[Gysbert_Wassenaar]

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.

[gouthamkk]

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

[Gysbert_Wassenaar · Accepted solution]

Yes, that's possible. Something like:

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