Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Re: Set Analysis reflecting the last result in the past.

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)

7 Replies

Re: Set Analysis reflecting the last result in the past.

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

Not applicable

Re: Set Analysis reflecting the last result in the past.

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
Contributor

Re: Set Analysis reflecting the last result in the past.

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


MVP
MVP

Re: Set Analysis reflecting the last result in the past.

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
Contributor II

Re: Set Analysis reflecting the last result in the past.

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

Re: Set Analysis reflecting the last result in the past.

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

Re: Set Analysis reflecting the last result in the past.

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

Community Browser