Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have table with from_date1,to_date1 and from_status. I have created one calendar for all dates.
Now I used IntervalMatch for creating table which will give status and calendar date.
from_status, to_status, from_date,to_date
Open, Closed, 01/01/2016,01/01/2016
After Interval Match
calendar date, from_status,from_date,to_date
01/01/2016, Open, 01/01/2016,01/01/2016
01/01/2016, Closed, 01/01/2016,01/01/2016
Required:
calendar date, from_status,from_date,to_date
01/01/2016, Closed, 01/01/2016,01/01/2016
for one date, Status got changed from Open to Closed so for this event, It is showing two entries in table which is correct but I want to show only second status (Closed) for that date
The FirstSortedValue() frunction against Status will return the value for only Closed as that is alphabetically first.
Here is the Help entry for it :
firstsortedvalue([{set_expression}][ distinct ] [ total [<fld {, fld}>]] expression [, sort_weight [, n]])
returns the first value of expression sorted by corresponding sort-weight when expression is iterated over the chart dimension(s). Sort-weight should return a numeric value where the lowest value will render the corresponding value of expression to be sorted first. By preceding the sort-value expression with a minus sign, the function will return the last value instead. If more than one value of expression share the same lowest sort-order, the function will return null. By stating an n larger than 1, you will get the nth value in order.
Examples:
firstsortedvalue ( PurchasedArticle, OrderDate )
firstsortedvalue ( PurchasedArticle, -OrderDate, 2 )
firstsortedvalue ( A/B, X*Y/3 )
firstsortedvalue ( distinct PurchasedArticle, OrderDate )
firstsortedvalue ( total PurchasedArticle, OrderDate )
firstsortedvalue ( total <Grp> PurchasedArticle, OrderDate )
where to use this in load script ?
No, your expression in the front end GUI.
using this in dimension but giving error
firstsortedvalue(From_status,-from_date) as I want last status for date.
No, I meant in your expression, not dimension.
but I require status in dimension only
No, not here
attaching sample.
See attached.
Is the object at the top left of Sheet1 what you are after ?