Say I have source data like the below: OriginDate|Name|IncidentDate| where Origin Date is constant and the earliest date of my imported data set. I've formatted the dates to numbers so I can perform calculations in query.

    AO       X         AP
5/19/2019 |Mary|5/21/2019
5/19/2019 |Lisa|5/29/2019
5/19/2019 |Lisa|5/29/2019
5/19/2019 |Lisa|6/1/2019

enter image description here

I've successfully created a google query pivot to get to the following:

=query('Source Data'!A1:AU,"SELECT AP,((SUM(AP)/COUNT(AP))-AO) group by AO, AP pivot X label ((SUM(AP)/COUNT(AP))-AO) ' ' ")

           Lisa    Mary
6/1/2019    13
5/29/2019   10
5/21/2019            2

enter image description here

What I'm trying to result is the calculated difference between Lisa's incidents on 6/1 and 5/29 or 3 days to know Lisa went 3 days between errors. The reason I'm dividing by count AP is that a pivot table adds dates together if there are multiple errors on the same day. I use the minimum date of the source data as the baseline on the sheet. That's the Origin Date value. I'm not sure it's needed but I could at least perform that subtraction and it worked.

My data is constantly updating so I'm using Importrange to get the data daily thus the need for a query.

Anyone got any clever ideas on how to get to this final step? I tried in excel to use diff from previous in a pivot table but could not get that to work either.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.