Announcements
cancel
Showing results for
Did you mean:
Partner - Specialist

## Median and Set Analysis

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?

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])

1 Solution

Accepted Solutions
MVP

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]))

7 Replies
MVP

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])

Partner - Specialist
Author

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

MVP

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

Best,

Sunny

MVP

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]))

Master III

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

Partner - Specialist
Author

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

Thanks again

Alexis

Partner - Specialist
Author

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!)

Community Browser