Vertical Symbol + Grouped Bar chart Question

ozarkram
ozarkram
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:

Best Answer

  • MichelleH
    MichelleH 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 :

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

Answers

  • ozarkram
    ozarkram

    Hi everyone, Any help on this will be appreciated!

  • mhouston
    mhouston 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.

  • ozarkram
    ozarkram

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

  • MichelleH
    MichelleH Coach

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

  • ozarkram
    ozarkram
    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

  • ST_-Superman-_
    ST_-Superman-_ Coach

    @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.


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • MichelleH
    MichelleH Coach

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

  • ozarkram
    ozarkram

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

    Vertical chart.PNG


  • ozarkram
    ozarkram
    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


  • MichelleH
    MichelleH 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 :

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

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

Categories