Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Sorting on Table Calculations with Row Limit Hit

  • 9 March 2018
  • 4 replies
  • 3442 views
J
Userlevel 1

Hi All,



I looked on Discourse for an answer or work around regarding this, but it looks like the last time this was discussed on this board was back in 2015 - so I am hoping there are some new developments (or new ideas) to resolve the issue I am running into.



I am building a dashboard where it will display week-over-week daily average revenue for the past 2 completed weeks, segmented out by our accounts.



For the purpose of this dashboard, we want to rank the accounts based on the week-over-week delta only. To calculate this I am pivoting the “week” dimension from my “date” dimension group, then using a Table Calculation to calculate % variance and the actual delta between each week.



Here is where the problem lies, the comprehensive list of our accounts is over 10,000 rows. Because row limit in Looker is 5,000 I am unable to sort on the calculated field. This is the general layout of the report that the team would like to see:




In the dashboard Visualization, it will display only the “Top 20 Losers” (negative delta) and “Top 20 Winners” (positive delta).



I looked for any filtered measures to calculate week over week for my revenue measure, but I wasn’t able to find anything that could give me this calculation via the LookML.



One final curve ball, this dashboard release will include a weekly PDF export (which is easy to set up), but the team also requested a Look where they can view and select a time frame (default is 2 completed weeks, but an analyst may want to see previous 4 weeks, or 5 weeks, etc). Again, I don’t think the second ask is difficult with the way I set up my data, but the inability to sort on the calculated field makes that Look pointless if we have over 10,000 accounts (rows).



We recognize that we can sort on the “Revenue” field to sort revenue in descending order, but that won’t give us the “Top Losers” and “Top Gainers” week-over-week - which is exactly what we need to monitor our accounts at a high level.



Are there any work around for this? Any feedback or ideas are welcomed.



Thanks!

4 replies

Aleksandrs_Vederniko
Rank
Userlevel 2

Hi @JLiou,



I hope you are doing good. As you mentioned above you cannot sort on table calculations that hit a row limit, there are few other limitations which are outlined Here.



For your use case I would suggest to use RANK() window function. You would need to do it with PDT we have some examples Here.



Best,



Sasha

powellandy
Userlevel 3

Hi



You can create the new measure directly in your model, rather than as a table calculation. That will allow you to sort as you do for a normal measure.



It would look something like this:



dimension: is_last_week {


   type: yesno


   sql: ;;--logic for order date is last week


}


dimension: is_this_week {


   type:yesno


   sql: ;;--logic for order date is this week


}


measure: revenue_last_week {


   hidden: yes


   type: sum #or avg, etc


   filters: {


      field: is_last_week


      value: yes


   }


}


measure: revenue_this_week {


   hidden: yes


   type: sum #or avg, etc


   filters: {


      field: is_this_week


      value: yes


   }


}


measure: revenue_change {


   type: number


   sql: (${revenue_this_week} - ${revenue_last_week}) / NULLIF(${revenue_last_week},0)


   value_format_name: pct_2


}


Best,


Andy

J
Userlevel 1

Thanks for the reply! This is helping me in getting pointed in the right direction. Couple of questions:



Sasha - For your suggestion, it looks like I need to create a rank function in a view itself. However, the “Explore” I am using is somewhat complicated and requires 4 other views joined together to get the “Explore” that I am using to rank revenue by account. Is there a way to create rank function within the explore post aggregations and segmentations?



Andy - Can you please elaborate on what you mean by “sql: ;;–logic for order date is this week”? Because, the relative week time frame I want to use is the built-in week filter (e.g. compare the week of 2/26 - 3/4 vs. 2/19 - 2/25). If I use the "sql: " I would have to specify a relative date timeframe (e.g.: ${date} > now() - 7; or something along the lines like this I would think; correct me if I am wrong here). Also, for this suggestion, is this put in the “Explore” or the “View”?



Thanks!

izzy
Userlevel 7
Badge +1

Hey @JLiou,



Jumping in here to help clarify.



You’re right about the order date logic-- A yesno dimension will return “yes” if the sql: clause evaluates to TRUE. So something like your example would work. This would go in the View file as a new dimension, of type: yesno.



For the rank function, when you implement it in the PDT as Sasha suggested, you can join in all the tables you need at that time, so all the fields you need will still be present. Here’s a little more info on PDTs to dig through if you need.



-Izzy

Reply


Terms of Service