Hello one and all!
I too have been looking for a dynamic way of re-creating sumif/countif in Looker calculations.
I’m using Looker for example, to show which US State a contact is living in. I have 1000 rows of contacts, each has a US State listed as a dimension (oh, and dropping the other dimensions is not a solution, we need the other dimensions)
Using MATCH(Dimenson1:Dimension1), you can identify the first instance of every US State, and with another calculation, you can INDEX that instance to produce a second clean list of every US State. So far, so good.
Where I hit a wall is here.
Let’s say the first states that show up in the above mentioned second column are NY, CA, and TX. Right now, I can only isolate the SUM one argument at a time: IF(NY, then NY, IF(CA, then CA, and so on. (This mostly takes shape around the row() of the result).
What I’ve not figured out yet is if you can lock what you’re looking at row by row in the second column, and have it look at every instance in the first column. In Excel, this is easy by applying a “$” symbol next to the row or column you want to keep.
So far in Looker, when I ask the second column to find what matches it on the first column, it only identifies instances where the item match on the same line, not across multiple lines.
Any idea if I’m missing something or this is what everyone has found?
Thanks for your interest. Have a nice day!