I am trying to create a calculated filed based on an if statement to categorize and add the sales in Looker. I have tried using multiple if statements but I am not able to figure out. I am not even sure if its possible in calculated field. Below is the data:
I need to create a calculated field to categorize the above styles and sub styles in a new column. My output should look like below:
This is possible to do just with table calculations, but it would involve quite a few steps. I wrote out a similar example on my instance. First, I used an if statement to create a new column for the total of each cohort we want to show. In my example, I wanted total by brand.
** The overall goal of this was to aggregate count of users by a limited number of brands.
I did have to create a new table calculation for each brand I wanted to show.
Then, I created a column that took the value of the new aggregated column and combined them based on the original brand name in the dimension field.
Then, I wanted to show only one line item per brand, so I sorted the data tab by the brand name and used the offset function with an if statement to determined if the line item above had the same brand name. If it did I returned no, else I returned yes. Then, I clicked hide no’s from visualization.
This was the explore I started with below:
This was my end result below!
We can try to combine a few of these steps, but I wanted to lay them all out just to make the logic as clear as possible. The alternative would be to remove the style and sub categories if those are dimension and then create a measure type sum for $$. Then, we get sum of sales grouped by New Style. We could create new style in the LookML layer with a
case when
statement.
This is possible to do just with table calculations, but it would involve quite a few steps. I wrote out a similar example on my instance. First, I used an if statement to create a new column for the total of each cohort we want to show. In my example, I wanted total by brand.
** The overall goal of this was to aggregate count of users by a limited number of brands.
I did have to create a new table calculation for each brand I wanted to show.
Then, I created a column that took the value of the new aggregated column and combined them based on the original brand name in the dimension field.
Then, I wanted to show only one line item per brand, so I sorted the data tab by the brand name and used the offset function with an if statement to determined if the line item above had the same brand name. If it did I returned no, else I returned yes. Then, I clicked hide no’s from visualization.
This was the explore I started with below:
This was my end result below!
We can try to combine a few of these steps, but I wanted to lay them all out just to make the logic as clear as possible. The alternative would be to remove the style and sub categories if those are dimension and then create a measure type sum for $$. Then, we get sum of sales grouped by New Style. We could create new style in the LookML layer with a
case when
statement.
