I wanted to do a calculation that turned out to be way more interesting than I wanted it to be.
I have the following:
date | sessions |
-----------------------
2020-03-01 | 400 |
2020-03-02 | 200 |
2020-03-03 | 600 |
2020-03-04 | 300 |
2020-03-05 | 100 |
2020-03-06 | 100 |
2020-03-07 | 50 |
2020-03-08 | 800 |
Then I add a running total table calculation
date | sessions | r. total |
----------------------------------
2020-03-01 | 400 | 400 |
2020-03-02 | 200 | 800 |
2020-03-03 | 600 | 1400 |
2020-03-04 | 300 | 1700 |
2020-03-05 | 100 | 1800 |
2020-03-06 | 100 | 1900 |
2020-03-07 | 50 | 1950 |
2020-03-08 | 800 | 2750 |
Now I would like to get the yesno table calculation at the 500 increments but of course it won’t hit it directly, hence I can’t use mod()
Is there any other way how I can get the row that’s the closest to the 500 increment? It would look like this:
date | sessions | r. total | is_500? |
--------------------------------------------
2020-03-01 | 400 | 400 | no |
2020-03-02 | 200 | 800 | yes |
2020-03-03 | 600 | 1400 | yes |
2020-03-04 | 300 | 1700 | yes |
And at the end I would like to calculate diff_days() between the previous yes.
Ideally I don’t want to add anything in the LookML since this is just a one-off thing but I welcome all suggestions.
LinkedIn
Twitter
