topic Max Date, by 1st Dimension, in Pivot in QlikView

Max Date, by 1st Dimension, in Pivot

Anonymous — Fri, 13 Jan 2012 19:14:09 GMT

This should be simple but I can't seem to get it. I need a pivot to only show the most recent invoice date, by customer. This set analysis expression doesn't do it: =sum({$<[InvoiceDate]={'$(=Max(Invoice_Date))'}>}[Sales Cases])

Max Date, by 1st Dimension, in Pivot

Anonymous — Fri, 13 Jan 2012 21:59:35 GMT

Ok, I think got it. It may not be the best way but it seems to be working.

=if([Invoice Date]=max(total <[Ship to Customer Number]>[Invoice Date]),sum([Sales Cases]))

Re: Max Date, by 1st Dimension, in Pivot

Miguel_Angel_Baeyens — Fri, 13 Jan 2012 22:41:55 GMT

Hi Brian,

Another option:

Sum(If(Aggr(NODISTINCT Max([Invoice Date]), [Ship to Customer Number]) = [Invoice Date], Cases))

It might worth testing how this expression performs.

Hope that helps.

Miguel

Re: Max Date, by 1st Dimension, in Pivot

Anonymous — Fri, 13 Jan 2012 23:26:21 GMT

That seems to work in most cases but not all. Not sure why. The chart on the right is using your expression.

Re: Max Date, by 1st Dimension, in Pivot

Miguel_Angel_Baeyens — Fri, 13 Jan 2012 23:34:48 GMT

Hi,

Probably because it needs another field [Ship To Customer Name] in the Aggr() function? Or may it be the NODISTINCT clause and account on purpose even when possible values for Max() are several in that aggregation?

Sum(If(Aggr(Max([Last Invoice Date]), [Ship to Customer Name], [Ship to Customer Name]) = [Last Invoice Date], Cases))

What the function does is very similar to yours, but using two aggregation functions which, for example, will return a total if there is a total line. The Aggr() part returns the Max() value of Last Invoice Date for each value in Ship to Customer Name, and if the value corresponds to the max, then Sum the values in Cases.

A FirstSortedValue() instead of Aggr() combined with the Sum() will likely work as well.

Hope that makes sense.

Miguel