Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Challengin in showing visual using "Yes" values in a Pivot table

  • 28 October 2018
  • 3 replies
  • 167 views
S

I have members in a cluster which are supposed to be equally loaded in an ideal situation. I am trying to trigger an alert if an appliance load dips 30% by creating a table calculation variable “Variation” with type yesno:

round(((${display_95_percentile}/offset(${display_95_percentile},-1)) - 1)*100,0)<-30


image


I do get the data (circled red). But when i try to hide “No” from visualization, the two rows with “Yes” also disappears in the visual (this could be because of other appliances in those rows showing “No”).


Because of this I am unable to create an alert that should trigger only when one of the cols become Yes. Any help is much appreciated.

3 replies

S

I have used another yesno variable:

to_string(pivot_row(${variation_30})) != “No,No,No,No,No,No,No”

to filter the rows that have “Yes”.


This is working fine: (squared green)

image


But looking for a better alternative.

cutler
Rank
Userlevel 5
Badge

I ran into a similar issue last week, also trying to alert on unusual events. What I did was pretty similar.


if(is_null(

match(yes, pivot_row(${is_bad_event}))

), no, yes)

The idea is the same: we want a cross-row table calc value that gives us a single yes or no for the entire row. I used match to flag cases of ${is_bad_event}. If it returns null, we know we’re in the clear. If there’s at least one value, we know there’s a bad event and the entire row is surfaced.


This approach seemed a little easier for me to wrap my head around, but now that I’ve written it up maybe that’s just because I came up with it. Not sure if it’s actually any more elegant.

S

@cutler Super . So, its match() 👏 I have been searching for phrases “regex, rex, search” etc for a search function. I will try this and let you know. One quick observation is we should be able to get rid of the if() statement by declaring the type as yesno.

Reply


Terms of Service