Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis reflecting the last result in the past.

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.

1 Solution

Accepted Solutions
Not applicable
Author

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)

View solution in original post

7 Replies
Kushal_Chawda

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

Not applicable
Author

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)

agustinbobba
Partner - Creator
Partner - Creator

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;


2016-03-18_16-16-27.png


Best Regards!


Agustin


maxgro
MVP
MVP

you can sort the concat by date and then pick the first one with subfield function

=SubField(

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

  '-'

  )

1.png

cjohnson
Partner - Creator II
Partner - Creator II

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

Not applicable
Author

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.

Not applicable
Author

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