Count items with max date

[#1]

Hi,

Can't figure out this simple problem:

DATES:
LOAD id, Date#(date) as date, status INLINE [
id, date, status
1, 1/1/2011, Rejected
1, 2/2/2011, Approved
2, 3/3/2011, Rejected
3, 4/4/2011, Approved];

I'm trying to create an expression to:

- count rows where date is max(date) for that id

I Know I can do it with a simple count distinct, but just because I'm curious I'm trying to get it to work with:

Count( {$<date={$(=max(date))}>} id )

and somehow this doesn't work. But if I similarly do:

Count( {$<id={$(=min(id))}>} id )

it works fine. I already noticed that dates get transformed into numbers, but cant get the expresion with date right.

regards Jeroen

[#2]

=if(date = max(total <id> date),id)

Depending on how you want to use it, you might have to use the aggr function.  That will only work in chart expressions, not the script.  You could also use the FirstSortedValue function.

Set analysis is calculated once per chart, not once per row so neither of those expressions you wrote are going to do what you really want (if you select one id then it will work, which is probably what happened when you tested the second).  Set analysis would be good if you wanted, say, all dates after 3/1/2011 (or something universal to every row).  Since each id has a different minimum date, you don't want set analysis.

[pat_agen]

hi Jeroen,

got it to work on my workstation but had to do two changes.

first in the load wrapped your date#() function which interprets in a date() function which formats. I formatted it to be equal to my default date setting.

SET DateFormat='DD/MM/YYYY';

DATES:
LOAD id, Date#(date) as date,
date(Date#(date),'DD/MM/YYYY') as date2,

status INLINE [

id, date, status

1, 1/1/2011, Rejected

1, 2/2/2011, Approved

2, 3/3/2011, Rejected

3, 4/4/2011, Approved];

then in the expression I put simple quotes round the max() expression changing the result from a number  to a string:

=Count( {$<date2={'$(=max(date2))'}>} id )

Does set analysis therefore require you to compare the string representation of the date? or is this always the case in qv. The date will be processed as a number when being transformed but in comparisons if not wrapped in a numeric function (num(), floor() etc.) must it be compared to a string?

I don't know the answer to this.

[#4]

@Trent, thanks for explaining set analysis is calculated once per chart. I am indeed trying to evaluate each row against the highest value of the corresponding group. So that means set analysis is out.

@Pat, thanks for clearing up the comparison with dates. Never thought of putting the expression between quotes. Although the date comparison works, I was expecting the answer to be 3 instead of 1. That probly is caused by set analysis being calculated only once like Trent said.

So a bit wiser, but not solved yet.

[#5]

Well if you have a chart with id as the dimension you do:

=count(if(date = max(total <id> date), id)

Not sure how this doens't solve your problem?

[#6]

I wasn't looking for the id with the latest date, I was looking for a count of all lines with a date equal to the latest date for that item.

[Miguel_Angel_Baeyens · Accepted solution]

Hello Jeroen,

Try something like the following

Count(If(Aggr(NODISTINCT Max(date), id) = date, id))

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#8]

Great that works . Why is the NODISTINCT required here?

[Miguel_Angel_Baeyens]

Hello Jeroen,

I'm using NODISTINCT because in this case the aggregation may return more than one result (higher date) according to the dimensions I'm using. By default, Aggr may return only one value, but in this case I want to get the "max of the max" values in date field.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[nerrazuriz]

Hello Miguel Angel,

I am using the following formula:

sum (If (Aggr (NODISTINCT max (Date), Local_Id, SKU) = Date, Value))

Which works perfect for the last date of the model. The problem is that when filtering by any date, the formula does not work in the same way.

Is there any way to leave it running in order to use the Date as a dynamic field?

thank you very much