Filtering or ordering visualisation only

done
low_priority
reply

(Dawid) #1

I am using a calendar table to list all months this year with table calculations. I need the whole table populated to correctly calculate cumulative sums but I would like to choose to display only the value for current month.

Is it possible?


(Izzy) #2

Dawid, I wonder if you can use the “Limit Displayed Rows” option in the visualization settings to do this. image
You can Hide/Show the first/last x rows from the viz, while maintaining the underlying results/calculations.


(Dawid) #3

I thought you’d said that. Unfortunately it’s neither first nor the last row that I need. It’s the row that matches something (eg. current month) but all rows have to be present in the data in order for everything to be calculated correctly.


(Andrew Powell) #4

Hi Dawid,

This is certainly possible - what you’ll need to do is create a Table Calculation to give a new dimension that returns yes on the row you want to show, and no on the row you don’t want to show. If you’re trying to show the current month then that would be quite straightforward.

You can then click the cog on the column heading and select ‘Hide Nos from Visualisation’, which should get you a visualisation with only the row you’re interested in

image

Best,
Andy


(Dawid) #5

Interesting, I did what you had suggested but I can’t see the option Hide Nos from Visualisation.

hide_nos


(Ian) #6

try “No” (capital N)


(Dawid) #7

Still the same, no extra option visible


(Jonathan Palmer) #8

Worth checking whether you’re returning a “No” string or the actual No value that Looker returns for boolean fields.


(Dawid) #9

My Calculation looks like the following:

if(${calendar_locales.month} = extract_months(now()), "Yes", "No")

I don’t know if I can cast it explicitly to boolean?


(Ben Silverstein) #10

Dawid - it’s actually simpler than that!

All you need to return a yesno on a table calc is a situation that will implicitly be true or false. If you just make your table calculation as ${calendar_locales.month} = extract_months(now()) (without the if setup), records that fit that condition will receive “Yes”, and records that don’t will receive “No”. Cheers!


(Dawid) #11

ooh of course it is! I knew I would try to overcomplicate it. Thanks Ben it works awesomely!


(Izzy) #12

You all have nailed it— One thing to add. If you do want to cast to boolean instead of having a statement that implicitly returns true/false, you can rewrite your initial function as

if(${calendar_locales.month} = extract_months(now()), yes, no)

with no quotation marks, and it’ll evaluate to the boolean yes/no instead of a string! But, @bens’ idea is just as good.