Subtract Same Timestamp Column in Different Rows - Set Analysis

[JayKay07]

Hi,

I am trying to calculate same Timestamp column in different rows from Ticket Created through Resolved (see expected output  below).  My set analysis expression is sum( {$<[Status]={'Ticket Created', 'Resolved'}>} [Start Date]- Above([Start Date]))

it's incorrect I get an error "Above function is not allowed inside aggregate".  Please note: I don't have permission to make changes in Load Editor. Can anyone please help me to write the set analysis expression?

[Saravanan_Desingh]

Please upload the Excel sheet to work upon

[edwin · Accepted solution]

 you dont need the above function:

=(only({<Status={'Resolved'}>}Start)-only({<Status={'Ticket Created'}>}Start) )*24

assuming you use ID as a dimension and that there is only one row per ID for create and resolved for the ONLY function to work

[JayKay07]

Edwin,

Thanks for response. Your answer works. I just had to remove *24 from expression.

=(only({<Status={'Resolved'}>}[Start Date])-only({<Status={'Ticket Created'}>}[Start Date]) )

One more thing, If I am adding this expression in the KPI chart I am seeing - in the KPI value unless I create a filter for ID and select a ID from filter. What would I need to make change in the expression to work in the KPI chart without selecting ID?

Again, Thank you so much for your help.

[JayKay07]

Saran,

Thanks for response. I attached the excel data file.

[edwin]

first off you need to decide what the KPI is - is it average?  you cant use the expression as is.  as i said ID needs to be in the dimension as it assumes only one value per field.  in a KPI where no ID is selected, the ONLY function will not work.

[edwin]

Assuming you want average, first you compute the difference per ID, then aggregate it and do an average:

=avg(
        aggr(
                      (only({<Status={'Resolved'}>}Start)-only({<Status={'Create'}>}Start) )*24,
        ID)
)

adding *24 or not is up to you depending on what you want to show (you can show days, hrs, minutes, sec, etc..)

[edwin]

again, remember the assumption is that there is only one recode for create and one record for resolve per ID otherwise the ONLY is fail for the specific ID but not for ones where the assumption holds

[JayKay07]

Thank you Edwin for your assistance. 

[edwin]

np