Median question

[alexis]

Hi all,

I have some performance issues and I am looking at ways of improving performance - one of the worst culprits is the below (especially Q4 the Median)

I am creating an application that is dealing with Transactions and Credit Cards

We need to answer the following questions (KPIs) for the time period and filters chosen:

Q1) Total number of transactions

Q2) Total number of unique cards used

Q3) Average number of transactions per unique card

Q4) Median transactions per unique card

To answer these questions I created variables as follows:

vTotalTransactionsSet1 

= sum({<

[numAuthorisationDate] = {'>=$(vStartDate1)<=$(vEndDate1)'},

[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],

[TerminalsID] = {'<> 00'},

[TransactionAmmount] = {'> 0'}

>} AuthorisationRec)

Please note:

a) I am using SUM of a record id field I created (value-=1) instead of COUNT for efficiency.

b) The [Group1] reference is because we use comparative analysis - I have the same set of formula for [Group2]

c) All the "calendar" fields are of the type [Authorisations...]

vTotalUniqueCardNumbersSet1

=count({<

[numAuthorisationDate] = {'>=$(vStartDate1)<=$(vEndDate1)'},

[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],

[TerminalsID] = {'<> 00'},

[TransactionAmmount] = {'> 0'}

>} distinct [CardNumber])

Q1) Total number of transactions:

= num($(vTotalTransactionsSet1))

Q2) Total number of unique cards used

= num($(vTotalUniqueCardNumbersSet1))

Q3) Average number of transactions per unique card

= num($(vTotalTransactionsSet1)

/  $(vTotalUniqueCardNumbersSet1))

Q4) Median transactions per unique card

= $(vMedianTransactionsPerUniqueCardSet1)

that is defined as:

= Median(Aggr(sum({<

[numAuthorisationDate] = {'>=$(vStartDate1)<=$(vEndDate1)'},

[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],

[TerminalsID] = {'<> 00'},

[TransactionAmmount] = {'> 0'}

>} AuthorisationRec)

/

count({<

[numAuthorisationDate] = {'>=$(vStartDate1)<=$(vEndDate1)'},

[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],

[TerminalsID] = {'<> 00'},

[TransactionAmmount] = {'> 0'}

>} distinct [CardNumber]), [CardNumber]))

[alexis]

For some reason the editor (whilst posting above) froze and I was not able to complete my question:

- Have I used the median correctly?

- Can you see more efficient ways of doing this?

Thanks in advance

Alexis

[swuehl · Accepted solution]

The divisor in your median aggr() expression should be always 1, thus it can be removed:

= Median(

Aggr(

sum({<

[numAuthorisationDate] = {'>=$(vStartDate1)<=$(vEndDate1)'},

[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],

[TerminalsID] = {'<> 00'},

[TransactionAmmount] = {'> 0'}

>} AuthorisationRec)

, [CardNumber]))

[alexis]

Thank you very much swuehi and apologies for taking so long to confirm but I was away from my computer at the weekend.

Can I kindly ask if you can email me as I need to ask you something that is not directly related to this question and therefore not of interest to someone reading the thread.

Thanks

Alexis