Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Discrete row totals

  • 31 January 2018
  • 7 replies
  • 849 views
T

I am new to Looker so please forgive my ignorance. I’ve done a lot of searching and my own experimentation on trying to solve this issue before posting.



I have a series of dimensions that I want to make calculations on to create custom aggregations, as I cannot modify the underlying LookML model. I have a Date dimension, and I want each column to represent the count of rows for that date which match the criteria in the calculation.



For example, let’s say I have three dimensions


JoinDate


IsGirl


IsNewMember



On the left I want dates, unique and ordered sequentially.



I create a calculation for NewGirls


sum(if(${IsGirl} = “Y” AND ${IsNewMember} = “Y”, 1, 0))



I get the same value for all rows, for all dates. I only want the sum of rows that match that criteria for each of the dates.



My other issue is that my date values get duplicated… so i’ll have like ten rows that have the same date, and the same count. How would I go about removing duplicate rows like that?

7 replies

T

running_total gets me almost what I want… except it’s cumulative instead of discrete. I don’t see a non-cumulative total function… and sum like I said just sums for the entire dataset, instead of for each row.

L
Rank
Userlevel 1

Hey there @twilliamsgsnetx.


The reason we are seeing duplicate rows in your example is because, for each join_date, there are different values for the is_girl field and the is_new_member field per each individual date.


For example, this is what my explore looks like when I just select field created_date from my view/table Users:




We see each individual distinct date, i.e. no repeated dates. Let’s look at what happens when I select a Yes/No dimension IsFemale (yes for users that are female):




We see that, in this table, the dates do get duplicated and this is because there are both male and female users created on the same days. So on 2018-01-28, for example, both male and female users were created. The same concept applies to your example. When I apply a similar table calculation, I get the same resulting behavior that you are seeing (displayed below) and that is because the calculation itself it not being grouped by the date or any other field, but rather generating that sum post query.




To achieve our goal here, we can filter the is_girl field and is_new_member field with front end filters without actually including them in the table. To do this, we would hover over the desired field and select FILTER without selecting the fields themselves, then we would choose the conditions we want our results to satisfy. In your example, something like is_girl is equal to Y and is_new_member is equal to Y. We can select join_date to include in the table and also select the count measure. This will group the counts by date for the results that satisfy the filter.



In my example, I am filtering created_date by is_california is Yes and is_female is Yes and selecting count. This is an example of how the explore should look:





To exclude the NULL values, we can select “Remove Filled in Dates” in the cog menu of the date dimension and run the query again. Or you can also include the other fields that you filtered by and just hide them from the visualization. This generally hides the records that correspond to the nulls that we initially see:


Option1:





Option2:




I hope this helps! Please let me know if there is anything I can clarify on my end!

T

My goal in doing it within calculations is that I want to add a bunch of disparate columns.



For the example you provide, I’d want the created date, but each column would be like



CaliforniaFemaleCount


CaliforniaMaleCount


NotCaliforniaFemaleCount


NotCaliforniaMaleCount



Every column being a unique set of filtering criteria.



I can accomplish this very easy in other BI objects, such as SAP BI, or general programming… but can’t seem to figure out how to accomplish this within Looker, without modifying the underlying LookML which I do not have permissions to do so.

L
Rank
Userlevel 1

Hey @twilliamsgsnetx!



To better understand the fields and the explore that we are working with in this example, could you please email a link to the explore with the current results to help.looker.com. This will ensure that we can provide the best support by looking at our use case and producing relevant workarounds that we can test on our end.



Thanks,



Leticia

mikhailxu
Rank
Userlevel 3

Hi @twilliamsgsnetx,



We keep business logic for metrics/measures in the LookML as a best practice because that creates consistency across all reports and dashboards used by different stakeholders in large analytics driven companies. This also allows us to quickly change business logic in a single place and update everywhere. I really recommend that if you’re creating this type of logic to get basic development privileges. It definitely sounds like you would be able to easily modify the LookML as it is built for general programmers.



We also do support more advanced patterns such as dynamic measures which allow for you to create custom measures out of template measures. You can then go nuts with different sets of filtering logic on each measure and then relabel them to whatever you want in the visualizer. This does require initial dynamic measures to be set up with the desired filter templates by a someone with LookML permissions.

DaanIF
Userlevel 3

Hi Tim,



Have you considered to put the “Is California” and “Is Female” dimensions in the PIVOTs?


This results in the four columns:


CaliforniaFemaleCount, CaliforniaMaleCount, NotCaliforniaFemaleCount, NotCaliforniaMaleCount



I believe that this would result in your desired counts. You can even get Row- and Column Totals. (So Totals for each date and Totals for each category)

T

Well, yes, I have. I started running into issues of performance and UI limitations adding more and more pivots. I have a lot of dimensions that need to be pivoted on.



It just seems like Looker isn’t really well suited, from a strictly non-LookML approach, to accomplish larger spreadsheets like I needed to recreate.

Reply


Terms of Service