Skip to content

    • Magic ETL

    Magic ETL

    Vertical Symbol + Grouped Bar chart Question

    Member
    in Magic ETL

    Hi All,

    I am trying to create a vertical symbol +Grouped bar chart to show by Fiscal Month week to show the On order $ for this year and last year and the on order units for this year and last year...

    Following are the beast modes that I am using

    X-Axis: Fiscal month week -CONCAT(UPPER(`FISCAL_PERIOD_LABEL_SHORT`),'-WK ', `WEEK_NUMBER_IN_FISCAL_PERIOD`) 


    Y-Axis: UNITS ON ORDER LY ORD DATE :

    CASE WHEN `FISCAL_YEAR` = `CUR_FISCAL_YEAR`-1 THEN COUNT(`units`) END


    Series: -UNITS ON ORDER TY ORD DATE

    CASE WHEN `FISCAL_YEAR` = `CUR_FISCAL_YEAR` THEN COUNT(`units`) END


    ON ORDER $$ LY ORD DATE

    CASE WHEN FISCAL_YEAR = `CUR_FISCAL_YEAR`-1 THEN SUM(`Amount`) END


    ON ORDER $$ TY ORD DATE

    CASE WHEN FISCAL_YEAR = `CUR_FISCAL_YEAR` THEN SUM(`amount`) END


    Under the Date range dropdown and the date range field I am using the field Common Date which has the beast mode as below:


    CASE WHEN `DATE_VALUE`< CURDATE() AND `DATE_VALUE`>= CURDATE() -364 THEN `date_value`

    WHEN `DATE_VALUE`< CURDATE() -364 AND `date_value`>= CURDATE() -728 THEN date_value + 364 


    WHEN `DATE_VALUE`< CURDATE() -728 AND `date_value`>= CURDATE() -1092 THEN `DATE_VALUE` + 728 

    ELSE `DATE_VALUE`END


    But I am getting fiscal month week getting displayed three times (please find attached the picture)..Can you please help me here..


    Diagram 1- $ and units.PNG legends.PNG


    Tagged:

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answer

    • Coach
      Answer ✓

      @ozarkram Try changing your beast modes on the Y axis and series so the case statements are within the aggregate.

      For example UNITS ON ORDER LY ORD DATE should look like this :

      1. COUNT(CASE WHEN `FISCAL_YEAR` = `CUR_FISCAL_YEAR`-1 THEN `units` END)

    Answers

    • Member

      Hi everyone, Any help on this will be appreciated!

    • Contributor

      @ozarkram How is the rest of your card configured? Do you have any sorting applied? I've had this happen if I've created a beast mode like that but then sorted on the actual date field.

    • Member

      Hi @mhouston : I am sorting by the field Week number in fiscal year...

    • Coach

      @ozarkram Do you have any sort of aggregation applied to your sort field? If not, try sorting by AVG of Week Number

    • Member
      edited June 2022

      @MichelleH : No I hadn't applied any aggregation...I tried applying the average of week number too...Still the week numbers appear thrice

    • Domo Employee

      @ozarkram - Could you provide a sample of data? It would be helpful to understand the structure better and to troubleshoot with schema that matches your dataset.

    • Coach

      @ozarkram What chart type are you using? That my also make a difference

    • Member

      @MichelleH : I am using the chart under vertical bar ->Symbol + Grouped bar..I will share the sample data soon

      Vertical chart.PNG


    • Member
      edited June 2022

      Hi @ST_-Superman-_ Please find below the data...Also attached the excel file..


      Input data

      image.png


      Expected output


      image.png


      Sample sheet for data.xlsx


    • Coach
      Answer ✓

      @ozarkram Try changing your beast modes on the Y axis and series so the case statements are within the aggregate.

      For example UNITS ON ORDER LY ORD DATE should look like this :

      1. COUNT(CASE WHEN `FISCAL_YEAR` = `CUR_FISCAL_YEAR`-1 THEN `units` END)
    • Member

      Thank you so much @MichelleH ...This works!..Really appreciate your help!

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories