Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
The title is as it is. I will explain.
I have a double issue with this set analysis.
to be able to use 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.
Hi kush, it doesn't work. There are several past surveys and I want just the last one.
I found a solution even if it's not really what I wanted.
=firstsortedvalue({<DateSurvey={"<=$(=date(today(),'DD/MM/YY'))"}>}SurveyResult,-DateSurvey)
=Concat({<DateSurvey={"<=$(=date(today()))"}>} SurveyResult,', ')
Hi kush, it doesn't work. There are several past surveys and I want just the last one.
I found a solution even if it's not really what I wanted.
=firstsortedvalue({<DateSurvey={"<=$(=date(today(),'DD/MM/YY'))"}>}SurveyResult,-DateSurvey)
Hi Alexandre!,
Here an example of what you need, you can use a flag in the script.
All:
LOAD * INLINE [
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
];
NoConcatenate
Table:
Load
Location,
SurveyResult,
date(MakeDate(Right(DateSurvey,4),mid(DateSurvey,4,2),Left(DateSurvey,2)), 'DD/MM/YYYY') AS DateSurvey
Resident All;
Left Join(Table)
Load
1 AS LastResult,
max(DateSurvey) AS DateSurvey
Resident Table
where DateSurvey <= Today()
Group by Location;
DROP Table All;
Best Regards!
Agustin
you can sort the concat by date and then pick the first one with subfield function
=SubField(
Concat({<DateSurvey={"<=$(=date(today()))"}>} SurveyResult, '-', -DateSurvey),
'-'
)
Hi Alexandre,
What is the issue with using firstsortedvalue? When looking at this problem that is the first function that came to mind. Does it not give you the results you were looking for?
The only thing I might add to this is putting a flag in the script to indicate which of the dates are in the past. You can then use that for set analysis instead. See attached for more details.
Thanks,
Camile
It's working perfectly. It's just that I would have prefered to learn how to use set analysis "in cascade" (if it exists) like:
sum({<DIM1={ DIM1={ DIM2={ } } }>}DIM0)
Thanks for the file. It's a good idea.
Hi Agustin,
Thanks for you reply. It's a good solution also. I'll try to use more flags in the future as it can simplify things a lot.
Best regards,
AC