Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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
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 | |
1 | 17/06/2016 | 100 | 72 |
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.
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)
If you need to learn about structured parameter and the issue with aggr() dimension sorting: