topic Median and Set Analysis in QlikView

Median and Set Analysis

alexis — Thu, 27 Aug 2015 12:56:09 GMT

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

Re: Median and Set Analysis

sunny_talwar — Thu, 27 Aug 2015 13:00:26 GMT

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

Re: Median and Set Analysis

alexis — Thu, 27 Aug 2015 13:07:18 GMT

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

Re: Median and Set Analysis

sunny_talwar — Thu, 27 Aug 2015 13:13:35 GMT

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

Best,

Sunny

Re: Median and Set Analysis

sunny_talwar — Thu, 27 Aug 2015 13:20:52 GMT

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

Re: Median and Set Analysis

sasiparupudi1 — Thu, 27 Aug 2015 13:33:32 GMT

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

Re: Median and Set Analysis

alexis — Thu, 27 Aug 2015 13:38:21 GMT

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

Thanks again

Alexis

Re: Median and Set Analysis

alexis — Thu, 27 Aug 2015 13:42:26 GMT

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