Double stage set analysis on string

[#1]

Hi,

The title is as it is. I will explain.

I have a double issue with this set analysis.

• First, the dimension I want at the end of the calculation is a string, not a number. (So I read I should use concat({.....}Dimension,','))

     to be able to use set analysis.

• Secondly, the set analysis is a 2 stages set analysis

Here is my case: I want the last result of a survey in the past (<Today).

The data is as below:

Location, SurveyResult, DateSurvey

Paris, Poor, 10/01/2015

Tokyo, Verygood, 15/02/2016

Paris, Verygood, 15/07/2015

Tokyo, Perfect, 23/02/2016

Tokyo, Undefined, 07/06/2017

Paris, Undefined, 03/06/2020

I tried something like this but there is only one condition, I miss the condition to say that this DateSurvey should be in the past.

=Concat({<DateSurvey={"$(=(date(Max(DateSurvey))))"}>} SurveyResult,', ')

Thanks for your help. I really tried my best before posting.

[#2]

This is the second part. My goal is to mix them.

=Concat({<DateSurvey={"<=$(=date(today(),'DD/MM/YY'))"}>} SurveyResult,', ')

[#3 · Accepted solution]

I found:

=firstsortedvalue({<DateSurvey={"<=$(=date(today(),'DD/MM/YY'))"}>}SurveyResult,-DateSurvey)

[alexpanjhc]

If you are looking only the past result. I found that in the 'limited' dataset, the surveyresult should be 'undefined', so you can use that in your set analysis?

[#5]

Yes indeed thank you. But I would have preferred to know how to merge two set analysis in one (dealing with the same dimension as a filter).