Best Of

so I added what you had suggested and my though was it would now in your example show the row for gn_id 1,111,111 with the max dispatchdate in this case being 5/28/19, correct?

attached is the way I currently have it, I have an inner join between loads and dispatch, loads contains gn_id, dispatch contains the dispatchdate

@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 - I was able to get the Comment and BAM columns to work, but the Sold_or_Stock_Date column is proving to be quite difficult in beastmode. Can you use ETL to manipulate the data?

Comment

case 
	when `orderstatus` LIKE '%*SLD%' then 'Sold'
    when `orderstatus` LIKE '%SLD%' then 'Sold'
    when `orderstatus` LIKE '%SOLD%' then 'Sold'
    when `orderstatus` LIKE '%STOCK%' then 'Stock'
    when `orderstatus` LIKE '%*STOCK%' and (`orderstatus` LIKE '%/%/%' or `orderstatus` LIKE '%/%') then 'Stock'
    when `orderstatus` LIKE '%STK%' and (`orderstatus` LIKE '%/%/%' or `orderstatus` LIKE '%/%') then 'Stock'
    when `orderstatus` LIKE '%*STK%' and (`orderstatus` LIKE '%/%/%' or `orderstatus` LIKE '%/%') then 'Stock'
    END

BAM

CASE 
	when `orderstatus` LIKE '%*%' then 'BAM'
    when `orderstatus` like '%BAM%' then 'BAM'
    when `orderstatus` LIKE '%/%/%' then 'BAM'
    when `orderstatus` LIKE '%/%' then 'BAM'
    END