new to set anlysis

[#1]

Hi,

I have a table for programs and statuses

Program     status     StatusNumber   IslastStatus  from_date          stasusname

P1               10               1                    0               01/01/2010          Stat10

P1               20               2                    0               15/03/2012          Stat20

P1               30               3                    1               18/06/2015          Stat30

P2               20               1                    0               20/12/2014          Stat20

P2               50               2                    0               18/04/2015          Stat50

P2               80               3                    1               03/01/2016          Stat80

i want to display in a chart for each prorgam:

Program

last status date

last statusname

status date before the lastone

statusname before the last one

i have problem with the 2 last fields...

i tried using in the expression  set analysis with

= aggr( max(  {$<StatusNumber   ={"=$(StatusNumber -1)"}> }  from_date )  ,Program  )

something is not working...

also what should be the expression for the "statusname before the last one" ?

thank you

[swuehl]

Try

=Max(from_date, 2)

resp.

=FirstSortedValue( statusname, -from_date,2 )

[sunny_talwar]

What is the expected output here?

[Chanty4u]

Max(Datefield)--->latest

Max(DateField,2)---->Previous

[Gysbert_Wassenaar · Accepted solution]

last status date: max(from_date)

last statusname: firstsortedvalue(stasusname, -from_date)

status date before the lastone: max(from_date,2)

statusname before the last one: firstsortedvalue(stasusname, -from_date, 2)

nb. make sure to use the exact case sensitive field names from your data model.

[#6]

Thank you. it worked!

[agustinbobba]

Hi Odelya!

Please remember mark the answer as correct

Best Regard!

Agustin

[#8]

Hi,

i don't see where to mark the answer as correct

where can i find it?

[sunny_talwar]

Look at this document to know how to mark correct and helpful answers -> Qlik Community Tip: Marking Replies as Correct or Helpful