Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

bgarland
Contributor III

Max Date, by 1st Dimension, in Pivot

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

latestinvoice.jpg

4 Replies
bgarland
Contributor III

Max Date, by 1st Dimension, in Pivot

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

MVP
MVP

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

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

bgarland
Contributor III

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

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

latestinvoice1.jpg

MVP
MVP

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

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

Community Browser