Vertical Symbol + Grouped Bar chart Question

ozarkram

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

MichelleH

@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

Hi everyone, Any help on this will be appreciated!

mhouston

@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

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

MichelleH

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

ozarkram

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

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

MichelleH

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

ozarkram

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

ozarkram

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

Input data

Expected output

Sample sheet for data.xlsx

MichelleH

@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

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