topic Re: Max timestamp in expression with if statement in QlikView

Max timestamp in expression with if statement

Thu, 12 Sep 2013 15:13:55 GMT

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?

Re: Max timestamp in expression with if statement

swuehl — Thu, 12 Sep 2013 15:20:28 GMT

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

Re: Max timestamp in expression with if statement

hic — Thu, 12 Sep 2013 17:15:09 GMT

Maybe

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

HIC

Re: Re: Max timestamp in expression with if statement

Thu, 12 Sep 2013 19:16:20 GMT

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.

Re: Re: Max timestamp in expression with if statement

Thu, 12 Sep 2013 19:20:39 GMT

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

Re: Re: Max timestamp in expression with if statement

hic — Thu, 12 Sep 2013 19:38:47 GMT

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

Re: Re: Re: Max timestamp in expression with if statement

swuehl — Thu, 12 Sep 2013 22:45:47 GMT

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?