Custom tiers in table calculation

[Dawid]

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.

[Cyril_MTL_Analy]

Hey @Dawid!
I think I got this:

Table Calc code:

• Running Total

running_total(${order_items.order_count})

• 500 Increments

floor(${running_total}/500)

• Yes-No

if(${500_increments}>offset(${500_increments},-1),"Yes", "No")

• Diff Days between increments

if(${yes_no}="Yes",
  diff_days(
    ${order_items.created_date},
    offset(
      ${order_items.created_date},
      row()-match(${500_increments}-1,${500_increments})
    )
  ),
  null
)

You’ll need to adapt to your fields obviously and you can combine them all into a single Table Calc.

[Dawid]

Ahh because the offset is not going to take the NULLs into account. That is a clever and cheeky solution @Cyril_MTL_Analy!

I will give it a whirl and report back but it looks brilliant!

[Dawid]

@Cyril_MTL_Analy the only thing is that the diff_days is NULL for the last increment, which doesn’t happen in your screen… that’s weird

I realised I had to flip the offset with (row() - match()) * - 1 and then the multiply the result again by -1.

Now it works. Here’s what I tried to achieve: