@here, Hi all!!!
I’m using the following sql script to obtain the top N from different categorical variables.
I need when I change the period of time, it changes the result I get.
So, I add in WHERE clause the condition for the date column.
But when I use the filter, the data doesn’t change.
Can someone help me with that? Thanks in advance!!
sql: SELECT field,
CASE
WHEN rank > {% parameter view1.param_rank_max %} THEN 'Other'
ELSE field
END AS topn
FROM
(
SELECT field,
cost,
dense_rank() over(ORDER BY cost DESC) as rank
FROM
(
SELECT {% parameter view1.analysis_variable %} AS field,
sum(cost) as cost
FROM table
WHERE {% condition view1.team %} team {% endcondition %} AND
{% condition view1.cost_center %} cost_center {% endcondition %} AND
{% condition view1.application %} application {% endcondition %} AND
{% condition view1.date_raw %} date {% endcondition %}
GROUP BY 1
)
)
Can you show us the SQL that’s generated with results that you see?
This is the result I got:
SELECT team AS field,
sum(cost) as cost
FROM table
WHERE (team = ‘Untagged’) AND
1=1 -- no filter on ‘view1.cost_center’
AND
1=1 -- no filter on ‘view1.application’
AND
1=1 -- no filter on ‘view1.usage_raw’
GROUP BY 1
the query is not considerer the date filter and I added it.
And how did you set up this filter? Show us the LookML
The LookML code is the one I copied in the first post.
sql: SELECT field,
CASE
WHEN rank > {% parameter view1.param_rank_max %} THEN 'Other'
ELSE field
END AS topn
FROM
(
SELECT field,
cost,
dense_rank() over(ORDER BY cost DESC) as rank
FROM
(
SELECT {% parameter view1.analysis_variable %} AS field,
sum(cost) as cost
FROM table
WHERE {% condition view1.team %} team {% endcondition %} AND
{% condition view1.cost_center %} cost_center {% endcondition %} AND
{% condition view1.application %} application {% endcondition %} AND
{% condition view1.usage_raw %} date {% endcondition %}
GROUP BY 1
)
)
The column in the table is date but in the view I called it usage.
Could it be the problem? I mean the difference in the name I used.
Yeah I would like to see the definition of that usage field as well