Median and Set Analysis

[alexis]

Hi,

I am developing an application where I have been asked to provide both averages and medians of certain values.

The formula below calculates the "average number of transactions per card" using set analysis and comparative and Alternate States (hence the [Group1] references.

I have a requirement to provide the "median number of transactions per card"  - can someone suggest a solution or an approach to do this?

Thanks in advance

Alexis

sum({<

[Authorisations Year] = [Group1]::[Authorisations Year],

[Authorisations Quarter] = [Group1]::[Authorisations Quarter],

[Authorisations Month] = [Group1]::[Authorisations Month],

[Authorisations Week] = [Group1]::[Authorisations Week],

[Authorisations Day] = [Group1]::[Authorisations Day]

>} AuthorisationRec)

/

count({<

[Authorisations Year] = [Group1]::[Authorisations Year],

[Authorisations Quarter] = [Group1]::[Authorisations Quarter],

[Authorisations Month] = [Group1]::[Authorisations Month],

[Authorisations Week] = [Group1]::[Authorisations Week],

[Authorisations Day] = [Group1]::[Authorisations Day]

>} distinct [Card Number])

[sunny_talwar]

Try this may be:

Median({<

[Authorisations Year] = [Group1]::[Authorisations Year],

[Authorisations Quarter] = [Group1]::[Authorisations Quarter],

[Authorisations Month] = [Group1]::[Authorisations Month],

[Authorisations Week] = [Group1]::[Authorisations Week],

[Authorisations Day] = [Group1]::[Authorisations Day]

>} AuthorisationRec/[Card Number])

[alexis]

Thanks for responding Sunny T but I am afraid this is not correct.

"AuthorisationRec" is a "counter" field (always has a value of 1) and I SUM it to get total number of transactions

"Card Number" on the other hand is an alphanumeric field and I calculate how many cards contribute in the total number of transactions by identifying the "COUNT" of UNIQUE card numbers.

Your division of 1/card number and the median of that does not make sense I am afriad.

Regards

Alexis

[sunny_talwar]

I understand what you are saying. While I think, somebody else might be able to help you out.

Best,

Sunny

[sunny_talwar · Accepted solution]

Not sure, but may be this:

Median(Aggr(sum({<

[Authorisations Year] = [Group1]::[Authorisations Year],

[Authorisations Quarter] = [Group1]::[Authorisations Quarter],

[Authorisations Month] = [Group1]::[Authorisations Month],

[Authorisations Week] = [Group1]::[Authorisations Week],

[Authorisations Day] = [Group1]::[Authorisations Day]

>} AuthorisationRec)

/

count({<

[Authorisations Year] = [Group1]::[Authorisations Year],

[Authorisations Quarter] = [Group1]::[Authorisations Quarter],

[Authorisations Month] = [Group1]::[Authorisations Month],

[Authorisations Week] = [Group1]::[Authorisations Week],

[Authorisations Day] = [Group1]::[Authorisations Day]

>} distinct [Card Number]), [Card Number]))

[sasiparupudi1]

if your counter is always one then your median will be 1 as well or am I reading incorrectly?

[alexis]

This looks promising Sunny T - let me verify the results before marking your answer as "Correct"

Thanks again

Alexis

[alexis]

Hi Sasidhar

The use of a "counter" field "1 As AuthorisationRec" is a good programming practise when you want to count the number of records because SUM(AuthorisationRec) is more efficient than COUNT(..)

Using Sunny's approach I am getting some promising results (and no they are not 1!)