Trying To Return A Value Using Set Analysis

[#1]

Hi -

I receive daily files.  Each file has an associated file date.  Each file contains transactions from multiple dates <= the file date. 

I have a calendar I created which lists the file date and the corresponding day of the month.  I am trying to return the day of the month which corresponds to the file File Date = the Maximum Transaction Date.  The FileDate and TranDate are text stings with the same format MMDDYY, i.e. 112613 is November 26, 2013.

MaxString(TranDate) does return the maximum transaction date, i.e. 112613.  I have tested this in a text box.

I was thinking in expression such as the one below might work, but it does not.

=p({<FileDate={MaxString(TranDate)}>} DayofTheMonth)

Thanks,

Mike

[swuehl]

You can use the p() function only in a set expression within an aggregation function. But I think you don't need p() here.

Try

=only( {<FileDate={$(=MaxString(TranDate))}>} DayofTheMonth)

[#3]

swuehl -

That doesn't seem to work, and the following portion of the expression you provided is grayed out if that means anything.

$(=MaxString(TranDate))

Thanks,

Mike

[swuehl]

The dollar sign expansion in gray and italic is fine.

What if you paste the number in:

=only( {<FileDate={112613} >} DayofTheMonth)

Do you get a number? Maybe using single quotes?

=only( {<FileDate={'112613'} >} DayofTheMonth)

If you can have multiple DayOfTheMonth values for the same max FileDate, then only() will return NULL.

If this could be the case, you need to decide which value you want to retrieve, maybe the maxstring:

=maxstring( {<FileDate={112613} >} DayofTheMonth)

[#5]

Hi swuehl -

Thanks for your quick responses. 

All three of these expressions return null "-".

=only( {<FileDate={112613} >} DayofTheMonth)

=only( {<FileDate={'112613'} >} DayofTheMonth)

=maxstring( {<FileDate={112613} >} DayofTheMonth)

But I don't think there are multiple DayofTheMonth values associated with each FileDate, because if I just put =DayofTheMonth in a text box and click on 112613 from a list of file dates, the text box populates with the number I am looking for.  This works consistently well for any FileDate I select including the max FileDate.

I've included a copy of the table viewer below is that helps.

Thanks,

Mike

[swuehl]

Hm, I don't see anything wrong, maybe I am missing something simple.

Could you upload your application?

http://community.qlik.com/docs/DOC-1290

[#7]

Hi swuehl -

See attached.

Mike

[swuehl · Accepted solution]

It's just a little typo (DayOfTheMonth instead of DayofTheMonth):

=only( {<FileDate={$(=Maxstring(TranDate))}>} DayOfTheMonth)

returns 21.