I have a fact table with “transactions”. Every transaction represent an “insert” or “withdrawal” of a product in a shelf. Every product/article have unique ID called TAG_ID. These inserts and withdrawals may happen any time and some articles are on the shelf for many days, sometimes weeks so no new transactions are made on these articles until something happens with the unique TAG_ID.
Now, I would like to calculate how many products or unique articles are in the shelf per day. Also, I would like to calculate how many inserts and withdrawals have been made per day. Every transaction have a timestamp so we now the exact time as well. It would also be nice to see this per hour.
Example of my data:
TAG_ID , TRANSACTION_TIME, SOURCE, DESTINATION
1234, 2018-11-01, User_1, Shelf_1
1234, 2018-12-10, Shelf_1, User_10
4567, 2018-12-01, User_2, Shelf_1
5678, 2018-12-02, User_10, Shelf_2
Any suggestions of how to do this in LookML? Anything pointing me in the right direction is appreciated. Thanks