Shape Gauges inside Mega Table?

user007486

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

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

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

@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

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">'
..