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: 
Anonymous
Not applicable

the desired formula select the last day on the field "start date"

Hi all,

There is a table

   Table1   

callsign priority start date completion date values
101a medical office 26.05.201431.12.20201
101a medical office 15.05.201516.05.20151
101back glass 26.05.201431.12.20201
101back glass 18.11.201431.12.20201
101brand 26.05.201431.12.20201
101brand 18.11.201431.12.20201
101resolution 26.05.201431.12.20201
101star 26.05.201431.12.20201

need formula to obtain the last sample value in the field "Stat date"

       Table2  

callsign priority start date completion date values
101a medical office 15.05.201516.05.20151
101back glass 18.11.201431.12.20201
101brand 18.11.201431.12.20201
101resolution 26.05.201431.12.20201
101star 26.05.201431.12.20201

thank you for your attention

1 Solution

Accepted Solutions
stigchel
Partner - Master
Partner - Master

First of all, make sure your dates are proper dates instead of strings, then you can use date functions and compare which date is older.

Then you can use in your expression like

if([start date]=Max(TOTAL <priority> [start date]),values)

You can see the result for the current situation by

if([start date]=MaxString(TOTAL <priority> [start date]),values)

View solution in original post

5 Replies
stigchel
Partner - Master
Partner - Master

First of all, make sure your dates are proper dates instead of strings, then you can use date functions and compare which date is older.

Then you can use in your expression like

if([start date]=Max(TOTAL <priority> [start date]),values)

You can see the result for the current situation by

if([start date]=MaxString(TOTAL <priority> [start date]),values)

stigchel
Partner - Master
Partner - Master

For the date load you can use this

Date(Date#([start date],'DD.MM.YYYY')) as NewDate

fvelascog72
Partner - Specialist
Partner - Specialist

Hi,

Try:

TMP1:

LOAD

  *

Inline [

callsign, priority, start date, completion date, values

101, a medical office, 26.05.2014, 31.12.2020, 1

101, a medical office, 15.05.2015, 16.05.2015, 1

101, back glass, 26.05.2014, 31.12.2020, 1

101, back glass, 18.11.2014, 31.12.2020, 1

101, brand, 26.05.2014, 31.12.2020, 1

101, brand, 18.11.2014, 31.12.2020, 1

101, resolution, 26.05.2014, 31.12.2020, 1

101, star, 26.05.2014, 31.12.2020, 1

];

Right Join (TMP1)

LOAD

  priority,

  LastValue([start date]) as "start date"

Resident TMP1

Group by priority;

Anonymous
Not applicable
Author

thank you for the quick reply, what you need, best regards

Anonymous
Not applicable
Author

Thank you, it needed the formula.

Thanks for the reply on the task