1

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
OriginDate|Name|IncidentDate
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.

0

Your Answer

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