Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

How to achieve conditional drill within the same pivot report

  • 24 January 2017
  • 6 replies
  • 586 views
R

Hi,


Below in the pivot table displaying units for staffs based on their allowances.(pivoted by dimension pay element)

I have added drill fields for the measure unit,to display details for the staffs when they click on the unit value.

However for certain pay elements such as for example for Incentive $1 or weekly elements, when they click on the unit,I require to hide certain columns such as the clock -in ,clock outs fields.


How do I achieve this?




  • measure: unit

    type: sum

    sql: ${TABLE}.value

    value_format_name: decimal_2

    drill_fields: [view_staff.username, view_staff.name, from_date, to_date, pay_element, unit,view_attendance_review.paid_work_duration,view_attendance_review.paid_clock_in_time_of_day,view_attendance_review.paid_clock_out_time_of_day, view_attendance_review.paid_break_duration_in_h,view_previous_roster.final_remarks]


On clicking the units below is the drill view ,how do I hide columns within the drill view for selected pay elements that is pivoted.



Let me know if there is an solution for this ,

Thanks!

6 replies

lloydtabb
Rank
Userlevel 6
Badge

I’m having a difficult time understanding the question. The simple thing is to remove the fields you don’t wish to see from the ‘drill_fields:’ parameter.


I think you are asking something else.

lloydtabb
Rank
Userlevel 6
Badge

Maybe this article will help?


Linking to URLs in drill menus (3.40+)

D
Rank

If I understand your question, what you’d like to do isn’t possible. The reason for this is that the drill fields are defined at the measure level, and since all your pivots work against a common measure, the measure drill fields come along as they’re defined in your LookML. Their definitions can’t be altered ‘on the fly.’

R

Hi,

If I remove the fields from the parameters links,as its a common measure,it will be removed also in the drill down for all the columns in the pivot table.

R

Hi Dave,


Thanks for the reply and the details that currently this feature isn’t available.

D
Rank

A colleague created a Liquid pattern that may allow you to accomplish the functionality you want. Here it is:


measure: count {

    type: count

    drill_fields: [id, orders.id, returned_date, orders.created_date]

    html:

      {% if link contains 'SALE' %}

      <a href = "{{ link | remove: ",order_items.returned_date"}}">{{rendered_value}}</a>

      {% elsif link contains 'RETURN' %}

      <a href = "{{ link | remove: ",orders.created_date"}}">{{rendered_value}}</a>

      {% else %}

      {{ link }}

      {% endif %}

      

      ;;

  }

One catch, following this example, is that if you filter on another field with the value ‘RETURN’ it will still remove the field. You may be able to write an IF statement to ensure you’re filtering the specific measure you care about on ‘SALE’ or ‘RETURN’ but this pattern gets you really close to your goal.

Reply


Terms of Service