Shape Gauges inside Mega Table?

user007486 · November 2020

Hello,

I'm creating a mega table showing previous month and current month and was wanting to create a column with up, down, no change icons and thought of the shape gauges. Is it possible to nest a shape gauge icon inside a mega table? Any advice on doing so would be appreciated.

Thanks,

Jason

GrantSmith · November 2020

Hi Jason / @user007486

You're not able to nest cards inside of cards however how I've done it in the past is to utilize some beast modes and some unicode characters like the following (this is calculating YOY % - you can adapt as necessary):

```

concat(
case
when round(SUM(`This Year` - `Last Year`) / SUM(`Last Year`) * 100, 0) > 0 then '<div><span style="color: green">'
when round(SUM(`This Year` - `Last Year`) / SUM(`Last Year`) * 100, 0) < 0 then '<div><span style="color: red">'
else '<div><span style="color: black">'
end,
case
when round(SUM(`This Year` - `Last Year`) / SUM(`Last Year`) * 100, 0) > 0 then ' ⇧ '
when round(SUM(`This Year` - `Last Year`) / SUM(`Last Year`) * 100, 0) < 0 then ' ⇩ '
else ''
end
,
round(SUM(`This Year` - `Last Year`) / SUM(`Last Year`) * 100, 0),
'%',
'</div>')

```

This highlights the color in red for negative, black for 0 and green for positive along with displaying an up or down arrow. It may not work in a Mega table but will work in an HTML table. - You could remove the <div> markups and forgo the coloring in favor of just the up and down arrows in a mega table.

GrantSmith · November 2020

Hi Jason / @user007486

You're not able to nest cards inside of cards however how I've done it in the past is to utilize some beast modes and some unicode characters like the following (this is calculating YOY % - you can adapt as necessary):

```

concat(
case
when round(SUM(`This Year` - `Last Year`) / SUM(`Last Year`) * 100, 0) > 0 then '<div><span style="color: green">'
when round(SUM(`This Year` - `Last Year`) / SUM(`Last Year`) * 100, 0) < 0 then '<div><span style="color: red">'
else '<div><span style="color: black">'
end,
case
when round(SUM(`This Year` - `Last Year`) / SUM(`Last Year`) * 100, 0) > 0 then ' ⇧ '
when round(SUM(`This Year` - `Last Year`) / SUM(`Last Year`) * 100, 0) < 0 then ' ⇩ '
else ''
end
,
round(SUM(`This Year` - `Last Year`) / SUM(`Last Year`) * 100, 0),
'%',
'</div>')

```

This highlights the color in red for negative, black for 0 and green for positive along with displaying an up or down arrow. It may not work in a Mega table but will work in an HTML table. - You could remove the <div> markups and forgo the coloring in favor of just the up and down arrows in a mega table.

user007486 · November 2020

@GrantSmith that's a nice idea. I'll see if I can make that work.

I'm realizing now my math is going to be a little complicated since I'm trying to get the difference of two ratios. I think that needs to be done via ETL or Dataflow, correct?

Anyway, thanks for the tip. I think that'll work.

jaeW_at_Onyx · November 2020

you don't necessarily have to hardcode the ratios into the data. In fact. Don't do that, it makes it so your card doesn't respond to filters.

instead use nested case statements

instead of ..

concat(
case
when round(SUM(`This Year` - `Last Year`) / SUM(`Last Year`) * 100, 0) > 0 then '<div><span style="color: green">'
...

try

concat(
case when

round(

SUM(case when ... numerator for this year) / sum(case ... denominator for this year)

-

sum(case numerator for last year) / sum( case denominator for last year) * 100, 0) > 0 then '<div><span style="color: green">'
..

Shape Gauges inside Mega Table?

Best Answer

Answers

Categories