Series Formatting (various types, i.e. Currency, Percentage, etc)

John-Peddle
John-Peddle Contributor
in Magic ETL

Just closed out a case w/Domo Support on this on this request; it's not built in functionality at this time though a good argument was made for it to be considered for a future release and it was submitted as an enhancement request. Were trying to show Total Cases (Y Axis, this is no problem), but then also show Total Gross Profit in $ (series 1) and Profit Margin Variance % (series 2).

 

Anyone happen to know of a work-around or "hack" that would allow a card to have different formatting for each series, not just one as it is currently (when you change any of the existing series, it updates the others to the format of the updated one, whether it be a number, percentage, currency, etc)

 

Thanks

Best Answers

  • AS
    AS Coach
    Answer ✓

    I think you'd want to consider using the Bubble chart in the Data Science chart category.

    You can have three measures in that chart, each with different formatting.

     

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • John-Peddle
    John-Peddle Contributor
    Answer ✓

    Aaron (@AS), thanks for the response. 

     

    Trying the Data Science Bubble Chart but in the original card we have the X Axis using a date and from what we're seeing we wouldn't be able to get a date to display in the test card as we are in the current card.

     

    Can you confirm whether or not it's possible for this type of chart (looks like it would have to be a specific period of time, i.e. All-Time, Current Month, previous Quarter, etc. but not Last 12 Months / By Month)?


    Attached screenshots are current card with data as we'd like to display (w/exception of displaying currency for both SERIES - the Y Axis is fine as a whole number and date is on the X Axis as mentioned). In the datas science card, would that be as closeas we can get to the existing card?

     

    We also added a quick filter so that managers can display by month, converting date to MONTH, YEAR but wanted to display the last 12 months from the current date, IN ORDER (any thoughts on the beast mode below, it's not working at this time)? Thanks, Aaron

    CASE WHEN DATE_FORMAT(`SoldDate`,'%M %Y') <= (CURDATE(),INTERVAL 12 MONTH) THEN `SoldDate` ELSE 'NA' END

    Screen Shot 2018-08-29 at 6.14.17 PM.png

     

    Screen Shot 2018-08-30 at 9.09.20 AM.png

  • AS
    AS Coach
    Answer ✓

    As far as the beast mode goes, if you're just trying to get the last twelve months, try something like this:

    CASE WHEN `SoldDate` >= DATESUB(DATE_FORMAT(CURDATE(),'%M %Y'), interval 12 month) THEN 'Yes' ELSE 'No' END

    That would give you a "Yes" for every row that is from the last twelve months. Use that in a filter.

     

    For the X Axis, you can definitely put that date there (or a monthly version of it). Combined with your date filter you could get 12 months on the X Axis.

    I haven't tested it extensively for this case, but as far as visualizations go, the bubble is one of the few charts that has more than two measures, which is what you need.  The other option that would definitely work is a table card.  You lose the nice visual, but you can format to your heart's content.

     

    One other thought, on your existing chart, you could remove the Y Axis labels and just leave the data labels on, then configure the labels from the Axis and Series menus.

     

     

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • AS
    AS Coach
    Answer ✓

    I think you'd want to consider using the Bubble chart in the Data Science chart category.

    You can have three measures in that chart, each with different formatting.

     

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • John-Peddle
    John-Peddle Contributor
    Answer ✓

    Aaron (@AS), thanks for the response. 

     

    Trying the Data Science Bubble Chart but in the original card we have the X Axis using a date and from what we're seeing we wouldn't be able to get a date to display in the test card as we are in the current card.

     

    Can you confirm whether or not it's possible for this type of chart (looks like it would have to be a specific period of time, i.e. All-Time, Current Month, previous Quarter, etc. but not Last 12 Months / By Month)?


    Attached screenshots are current card with data as we'd like to display (w/exception of displaying currency for both SERIES - the Y Axis is fine as a whole number and date is on the X Axis as mentioned). In the datas science card, would that be as closeas we can get to the existing card?

     

    We also added a quick filter so that managers can display by month, converting date to MONTH, YEAR but wanted to display the last 12 months from the current date, IN ORDER (any thoughts on the beast mode below, it's not working at this time)? Thanks, Aaron

    CASE WHEN DATE_FORMAT(`SoldDate`,'%M %Y') <= (CURDATE(),INTERVAL 12 MONTH) THEN `SoldDate` ELSE 'NA' END

    Screen Shot 2018-08-29 at 6.14.17 PM.png

     

    Screen Shot 2018-08-30 at 9.09.20 AM.png

  • AS
    AS Coach
    Answer ✓

    As far as the beast mode goes, if you're just trying to get the last twelve months, try something like this:

    CASE WHEN `SoldDate` >= DATESUB(DATE_FORMAT(CURDATE(),'%M %Y'), interval 12 month) THEN 'Yes' ELSE 'No' END

    That would give you a "Yes" for every row that is from the last twelve months. Use that in a filter.

     

    For the X Axis, you can definitely put that date there (or a monthly version of it). Combined with your date filter you could get 12 months on the X Axis.

    I haven't tested it extensively for this case, but as far as visualizations go, the bubble is one of the few charts that has more than two measures, which is what you need.  The other option that would definitely work is a table card.  You lose the nice visual, but you can format to your heart's content.

     

    One other thought, on your existing chart, you could remove the Y Axis labels and just leave the data labels on, then configure the labels from the Axis and Series menus.

     

     

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • John-Peddle
    John-Peddle Contributor

    Thanks for the response, Aaron (@AS). Tried that beast mode but it's only returning NO values (added the underscore in DATESUB as you mentioned in another thread). Any thoughts on that?

     

    For this type of chart, we can only have one series, whether you aggregate the Y Axis or not, correct? In order to achieve a visual that's easily read when we added DocumentType to BOTH the Bubble Size and Series - but when using SoldDate on the X Axis, we aren't able to add DocumentationType to both the Bubble Size & Series, which is what we had in the previous screenshot.

     

    Screen Shot 2018-08-30 at 2.02.10 PM.png

Categories