topic Re: Difference when using Max() or Min() in QlikView

Difference when using Max() or Min()

Tue, 14 Apr 2015 13:18:25 GMT

Hi!

For our sales team I want to display a chart that shows months as dimension and the counts of first as well as last orders of customers as Y-values. So first order means "new customer" and last order shows when the last order of a customer was placed.

Now I figured out how to count the first orders:

=Sum(If(Aggr( Min([Posting Date]), Name) = [Posting Date], 1))

Result: 39 New Customers spread across serveral months

But when I try to do the same for last orders by exchanging Min for Max, the result ge

Latest Orders:

=Sum(If(Aggr( Max([Posting Date]), Name) = [Posting Date], 1))

Result: Only 12 Last Orders.

Logically thinking, every Cusomer should have a first and a last order. So both values should have the same sum in total.

I just don't get the mistake... 🙂

Thanks a lot in advance!!
Timm

result table (diagram):

YearMonth	last order	first order
	12	39
201208	0	2
201209	1	3
201212	1	1
201302	0	1
201304	0	2
201305	2	3
201307	2	3
201308	0	1
201309	0	1
201310	3	5
201312	0	1
201401	0	2
201403	0	2
201405	0	3
201407	0	2
201409	1	4
201410	0	1
201412	1	1
201503	1	1

Re: Difference when using Max() or Min()

marcus_sommer — Tue, 14 Apr 2015 16:15:06 GMT

I don't think it will be the same result and your expression compared an aggregation directly with a field without aggregation which will be always return NULL if there isn't a unique value. I believe your expression should be more look like this:

=Sum(Aggr(sum({< [Posting Date] = {"$(=Max([Posting Date]))"}>}1), Name, YearMonth))

- Marcus

Re: Difference when using Max() or Min()

ramoncova06 — Tue, 14 Apr 2015 16:20:41 GMT

if I understood right, what you want to do is to get the min and max posting date for each customer, right ?

=Sum(If(Aggr( Max(total <Name>[Posting Date]), Name) = [Posting Date], 1))

=Sum(If(Aggr( Min(total <Name>[Posting Date]), Name) = [Posting Date], 1))