Dates and set analysis

[#1]

I am trying to include a date check as part of an expression using set analysis but have been unsuccessful. So I tried to use some other ways to get the results I need in my chart that are also not working.

In my example, I am showing a row in a straight table for each person and some data that was entered about that person in multiple columns. The expression for each column looks something like this:

only({$ <QuestionCTReferenceId = {'Sensory_qu'} >} Results )

Here I am getting the results entered for this particular question. Each question then is its own column so the expressions are similar. In addition to the person and results column, I am also showing a date for when the data was entered which I currently have setup as a dimension. This is all working fine but I am getting a person showing up each time a new set of data was entered. What I would like to show is only the data with the latest date.

I was able to filter out the old dates but then my columns need to include a date check to only show the latest results. I have tried to include a date check in the expression above but that did not work.

if(aggr(max(ObserveDate), PersonID),only({$ <QuestionCTReferenceId = {'Sensory_qu'}>} Results))

I was then trying other ways to hide the rows of data I do not want to show. I created a calculated dimension for the date that I was hoping would then allow me to checkmark "suppress when null" to hide the extra rows but I am having problems with that as well.

=If(ObserveDate >= aggr(max(ObserveDate), PersonID), Date(ObserveDate, 'M/DD/YYYY'), null())

The same thing used as an expression instead of a calculated dimension works great but then I cannot suppress the null values.

Any ideas on how to get this to work would be extremely helpful! Thank you!

Do you have any suggestions on how I may be able to get this accomplished?

[#2]

Hi,

I would suggest calculating a flag during LOAD which marks the latest set of data for each person; you can then filter on this flag instead of creating all kinds of aggregates on-the-fly (which probably will cause performance problems because they have to be calculated for each cell in your straight table).

Assuming that one single set of data describes answers from one person on one single date, and for each person you want to flag the answers in the data set with the latest date, I'd expect something like:

1. select date of latest data set for each person, and some flag value (resident load person, max(date) as date, 1 as flag from original_data group by person) into temporary table
2. join this temporary table to the original data (should match on date and person fields) -> the original data now contains a 'flag' column, containing value 1 for each row that's linked to the last date for some person

Hope this helps; I'll leave the exact syntax as an excercise for you

Martijn