Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Pivot Measure's sort order keeps changing

  • 31 January 2018
  • 2 replies
  • 952 views
J
Userlevel 1

Hello,



I am new to Looker, so apologies if this is a dumb question. I have searched online, in this Discourse, and attempted to resolve this on my own - but to no avail.



I am building a dashboard which contains a table for its Visualization, with 2 dimensions and the rest of the measures are all calculated fields. The issue I am running into is that when I sort my data in the “Results” section below the “Visualization” section, the pivot measure (date) keeps the sort order as it is (which is what I want). However, when I sort my data in the “Visualization” section above the “Results” section, the pivot measure’s order (date) changes.



As you can imagine, this messes with my “Previous 7 day Revenue” table calculation (using the “pivot_index(${field}, 1) + pivot_index(${field}, 2) + … + pivot_index(${field}, 7)” function). To note, the previous 7 day revenue field is a rolling 7 day; with data up to Tuesday, it compares the current Wed - Tues vs. the previous Wed - Tues. Tomorrow, it will be Thurs - Wed vs previous Thurs - Wed, etc.



This is the layout of the “Results” pane, the columns noted as “Shown” will display in the Visualization while the “Hidden” columns are not shown. I want the sorting to be the most recent date on the far left while 10/1/2017 on the far right. Everyday, it will append a new date with data to the far left. Right now, the far left date is 1/30/2018, but tomorrow it will be 1/31/2018, and so on and so forth:




(Apologies for the excel image, I didn’t want to show my company’s proprietary information but wanted to get my point across with some visuals)



Is there a work around or is there a reason why this keeps happening? It makes it difficult for me to send this dashboard out to anyone since I need to always tell them to never sort any of the data tables.



Thanks!

2 replies

jeremy.eckman
Rank

@JLiou - Thanks for the visualization to help with your explanation, it provided great context! It sounds like the issue is that if someone goes to sort any of the columns they see in the visualization, it causes the sort to be applied to that column and removes it from the sorted pivot that they don’t see. Currently, there is not a way to ‘lock’ the sort order to prevent the order from changing.



A filtered measure might be a good approach to the sum fields (link to docs on filters)


Something like this could be used to calculate the sum of a field, and the measure has the filter in it that keeps it to the last 7 complete days:



measure: total_profit_last_week {


type: sum


sql: ${order_profit} ;;


filters: {


field: created_date


value: “7 days ago for 7 days”


}


}



With measures like this, you could have the % Variance in the table calculations and they would not be dependent on pivoted columns being in the proper order.



If you would like some further assistance or have additional questions in implimenting this, please reach out to us at help.looker.com.

J
Userlevel 1

Thanks @jeremy.eckman, this code worked like a charm. However, this brings up another issue that I have been trying to figure out that is somewhat related to what I am trying to do here.



When we set the filter value as “7 days ago for 7 days” it will reference our BigQuery (we are in Legacy) timezone - which is UTC. However, we operate and report on PST. So, at 4:00PM PST it is technically the next day if Looker is referencing UTC time, and so the filter logic of “7 days ago for 7 days” is then looking at the past 6 days for us (PST) if we look at this report at night.



Is there a work around to convert the built in filter to reference PST rather than the BigQuery timezone (UTC)?



Here is an example of what I mean, I pulled this data at 6:47PM PST 2/1/2018 (which is then 2:46AM 2/2/2018 UTC):




This is why I am pivoting all of my data and using “pivot_index(${field}, 1) + pivot_index(${field}, 2) + … + pivot_index(${field}, 7)” function. Therefore, if I pull the report I created now it will still reference 1/24/2018 - 1/30/2018 until 1/31/2018 is populated and wont consider 2/1/2018 as one of the previous days.



This also creates an issue if we use the filter “is in the past 1 months” since someone looking at the report in the evening of the 1st of each month will see no data since Looker will be referencing UTC (which will be the 2nd of the month).



Thanks!

Reply


Terms of Service