Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hello Jeroen,
Try something like the following
Count(If(Aggr(NODISTINCT Max(date), id) = date, id))
Hope that helps.
BI Consultant
=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.
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.
@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.
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?
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.
Hello Jeroen,
Try something like the following
Count(If(Aggr(NODISTINCT Max(date), id) = date, id))
Hope that helps.
BI Consultant
Great that works . Why is the NODISTINCT required here?
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.
BI Consultant
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