How to get a User's start and complete date time for each of the unique IDs of a product?

[alespooletto]

Hello, I have a QVD file that contains the following information:

• User
• Unique ID of a product
• DateTime 
• A flag with either Complete or Start 

Now, I want to show in a table, at what time did each of the users start and complete a certain product. I tried using a Pivot table, but this seems unnecessary and expansive in computational terms. Is there an easier way to do this?

Ideally, it shoul look like this:

[vincent_ardiet_ · Accepted solution]

You can create a straight table with User and SFC has dimensions, then 2 measures:

=Min({<Flag={Start}>}DateTime)
=Max({<Flag={Complete}>}DateTime)

[alespooletto]

Thank you, that was a quick and simple fix. 

[alespooletto]

Sorry, I don't want to open yet another thread for this question. Could you please help me figure out, is there a way to obtain an average duration of the difference between these two fields you suggested me here?

[vincent_ardiet_]

Like this?
Avg(Max({<Flag={Complete}>}DateTime)-Min({<Flag={Start}>}DateTime))

[alespooletto]

Thank you, sorry I just got back from the holidays. It seems like the measure doesn't work, as nested aggregations are not allowed, but I will work on a solution myself starting from this. 

[vincent_ardiet_]

Yes, my bad, you need to use AGGR in this case:
Avg(Aggr(Max({<Flag={Complete}>}DateTime)-Min({<Flag={Start}>}DateTime),User,SFC))