Max timestamp in expression with if statement

[#1]

I've looked around the community for an answer to this but nothing's worked that I've tried for my particular situation. I need this expression in a chart, but since there's multiple HISTORY_UPDATE_DATE timestamps per REQUEST_ID I have to grab the maximum value (most recent) HISTORY_UPDATE_DATE.

=COUNT(IF(FABS((TODAY()-HISTORY_UPDATE_DATE)) < 1, REQUEST_ID))

Any suggestions?

[swuehl]

Maybe just do a DISTINCT count?

=COUNT(DISTINCT IF(FABS((TODAY()-HISTORY_UPDATE_DATE)) < 1, REQUEST_ID))

or another possibility might be:

=sum( aggr( if( fabs(Today()-max(HISTORY_UPDATE_DATE)<1,1), REQUEST_ID))

[hic]

Maybe

Count( If(HISTORY_UPDATE_DATE=Max(total HISTORY_UPDATE_DATE), REQUEST_ID))

HIC

[#4]

The distinct count didn't narrow down the data to one timestamp oddly enough, and there seems to be a syntax error with the aggregation option. I've attached some sample data if it helps. To further explain what I have to do - I need to get counts of all of REQUEST_IDs whose most recent timestamp is less than 1 day, between 1 & 3 days, and more than 3 days. Any other info needed, I'll be happy to supply.

[#5]

I need the <1 part in there as a comparison function. I tried a modified version of what you suggested, but neither your original or this modified versin worked:

=Count( If(Today()-(HISTORY_UPDATE_DATE=Max(total HISTORY_UPDATE_DATE)) < 1, REQUEST_ID))

[hic]

The expression HISTORY_UPDATE_DATE=Max(total HISTORY_UPDATE_DATE) does the necessary comparison, or ...? So, I don't understand why you need an additional relational operator.

HIC

[swuehl]

The distinct count didn't narrow down the data to one timestamp oddly enough, and there seems to be a syntax error with the aggregation option. I've attached some sample data if it helps. To further explain what I have to do - I need to get counts of all of REQUEST_IDs whose most recent timestamp is less than 1 day, between 1 & 3 days, and more than 3 days. Any other info needed, I'll be happy to supply.

What do you expect as result?

I assumed you want to get a distinct count of the REQUEST_ID with a max HISTORY_UPDATE within a certain distance from today.

You posted a sample with three distinct REQUEST_ID, so the answer should be 0,1,2 or 3.

Looking at your data, two REQUEST_IDs 784445 and 784596 have a maximum HISTORY_UPDATE_DATE within 1 day from today, so should be counted -- answer is 2.

Using my first expression with the DISTINCT qualifier, I do get these two IDs counted.

Using my second expression, after adding the missing closing bracket, I also get a count of 2.

Again, what do you expect to see when using your sample data?