Calculations on fact table with events


(Steven Ivanoff) #1

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:
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

(Izzy) #2

A calculation of how many inserts/withdrawals have been made per day should be as simple as a measure of type:count— Assuming you have a primary key defined, that should give you the count of transactions, and by adding in the dimensions for hour/week/day/year you’ll see the appropriate counts.

Calculating how many products are on the shelf would be similar, but you’d probably want to do a count_distinct since you don’t want to double count a product for every time it was involved in a transaction. Check out those two links and see if they help!