11 Replies Latest reply: Jan 6, 2016 11:02 AM by Bill Markham

# Intervalmatch- two event in one interval

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

• ###### Re: Intervalmatch- two event in one interval

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 )

• ###### Re: Intervalmatch- two event in one interval

where to use this in load script ?

• ###### Re: Intervalmatch- two event in one interval

No, your expression in the front end GUI.

• ###### Re: Intervalmatch- two event in one interval

using this in dimension but giving error

firstsortedvalue(From_status,-from_date) as I want last status for date.

• ###### Re: Intervalmatch- two event in one interval

No, I meant in your expression, not dimension.

• ###### Re: Intervalmatch- two event in one interval

but I require status in dimension only

• ###### Re: Intervalmatch- two event in one interval

No, not here

• ###### Re: Intervalmatch- two event in one interval

attaching sample.

• ###### Re: Intervalmatch- two event in one interval

See attached.

Is the object at the top left of Sheet1 what you are after ?

• ###### Re: Intervalmatch- two event in one interval

hi,

I want this in my chart with date and status as dimension. see first tab line chart?

• ###### Re: Intervalmatch- two event in one interval

Is this closer to what you want ?

If uses the aggr() function in the expression:

=count(aggr( max(From_Date) , IssueID, CalendarDate))

I changed it from a Line Chart to Bar Chart, as the sparse sample did not visualize well as lines.  It could well visualize better with your real data.