I'm working on an app with the following structure :
Table:
Date --- SerialNumber ---- Site
01/01/2019 serial1 Paris
01/01/2019 serial2 Paris
01/01/2019 serial3 London
08/01/2019 serial1 Paris
08/01/2019 serial2 Paris
08/01/2019 serial3 London
15/01/2019 serial1 Paris
15/01/2019 serial2 London
15/01/2019 serial3 London
What I want to do is to calculate, for each date I have, for each site, the count of serial number (ie. distinct device) that entered or left. It can be either in script or graph calculation directly in the app, I can change both.
In the example I gave above, it would mean :
For 01/01/2019, I'd have 0 entries / exits cause there is no date before
For 08/01/2019, Paris +0 and -0, London +0 and -0
For 15/01/2019, Paris +0 -1 (cause serial2 left Paris) and London +1 -0 (because no serial left but one entered London, the serial2)
In the real table, I have over 100 sites and 300.000 serial number per day.
I've been trying to figure this out for a couple of days and I can't think of anything, help would be gladly appreciated.
