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: 
santiago_respane
Specialist
Specialist

Get Latest Changed Value with Set Analysis

Hi Experts,

i am facing the following issue, i have to display the current value of product and the last changed value.

Important: I need the latest value that changed, not the value from the Current Date - 1

Prod      Date               Value

1           17/06/2016      100

1           15/06/2016      100

1           14/06/2016      100

1           13/06/2016      100

1           12/06/2016      72

1           11/06/2016      72

And the desired output must be:

Prod     Date               Current Value     Last Value

1          17/06/2016      100                    72

I can see the solution via scripting but, is this possible with set analysis?

Any help will be appreciated.

Thanks in advance.

Kind regards,

4 Replies
sunny_talwar

May be try this:

Dimension: Prod

Expression:

1) Date(Max(Date))

2) FirstSortedValue(Value,  -Date)

3) FirstSortedValue(Value, Date)

You want the last changed value and not the last value

swuehl
MVP
MVP

Maybe something like

Prod Date(Max(Date)) FirstSortedValue(Value,-Date) =FirstSortedValue( Aggr( If( Value <> TOP(Value),Value), Prod,Date), -Aggr(If( Value <> TOP(Value),Date),Prod,Date) )
17/06/2016 100 72
117/06/201610072

edit:

Your Date values would need to have a chronological load order (either ascending or descending, you can adjust the TOP() / BOTTOM() and minus signs).

Or if you are using QV12 or sense, use structured parameter with the aggr() function.

sunny_talwar

May be a hybrid approach:

Table:

LOAD * Inline [

Prod,      Date,              Value

1,          17/06/2016,      100

1,          15/06/2016,      100

1,          14/06/2016,      100

1,          13/06/2016,      100

1,          12/06/2016,      72

1,          11/06/2016,      72

1, 10/06/2016,  82

];

FINAL_TABLE:

LOAD *,

  If(Prod = Previous(Prod) and Value = Previous(Value), Peek('Flag'), RangeSum(Peek('Flag'), 1)) as Flag

Resident Table

Order By Prod, Date;

DROP Table Table;

Dimension:

Prod

Expressions:

FirstSortedValue(DISTINCT Value,  -Flag)

FirstSortedValue(DISTINCT Value,  -Flag, 2)

Capture.PNG

swuehl
MVP
MVP

If you need to learn about structured parameter and the issue with aggr() dimension sorting:

The sortable Aggr function is finally here!