Dimension to categorize row based on shared ID

  • 14 January 2020
  • 0 replies

Hi Everyone,

I apologize if there is a solution to this elsewhere in the forums, every thread I found seemed to end with conversations moving to email. I’m not a Looker admin unfortunately so this won’t be an option for me.

I am trying to recreate IF and COUNTIF equations I use in Excel, you can see the columns (F - I) here.

In our database a household made up of one or more clients shares a “Household ID” among all members of the household for the duration of their enrollment in a project. Our database vendor has “Household Type” dimensions that capture a household’s type (Households with Children, Households without Children, or Households with only Children) at the time they enter the project or as composed on another screen. Neither of these fields capture changes in household composition which is why we use these Excel formulas.

I have not been able to figure out a way to make Looker look at a household’s Household ID and determine how many rows with the same Household ID have an age of “18 or older” or “under 18.”

The closest example I’ve found to this problem is in this thread, but as in the OP’s example it is not possible for us to include every possible household ID in the dimension.

We would appreciate any suggestions on how to do this!


0 replies

Be the first to reply!