Best Of

From a syntax perspective, you are missing an END statement. You only have one, but you need two because you have two CASE statements.

What's your dataset structure like? If you can get it to a row per day worked, you could join to the calendar dimension, and then do a group by on month and year to count the number of days worked.

Does your dataset include future fiscal years so that you need the where clause for "DATE_VALUE" = current_date? if not you could just use a group by tile (you may need to add a constant tile before hand with a dummy constant to group on) and get max(fiscal_year).

Otherwise you could use the filter tile to filter where date_value = current_date, and then do the group by tile to get the max. Then you can join back to your dataset.

You can use a case statement to evaluate this and look for SLD and SOLD. It would look like this:

(CASE when `orderstatus` like '%SLD%' THEN 'Sold'
when `orderstatus` like '%SOLD%' THEN 'Sold'
ELSE 'Stock'
END)

Solved my own issue with a Dynamic Unpivot

I've done a writeup of lag functions and rolling averages before utilizing Beast Modes but the idea at the end is the same concept. You just need to calculate the Lag for 1 month ago and 2 months ago into three separate fields. Then add all 3 together (current month, 1 month ago and 2 months ago) and divide the total by 3 using a formula tile. That will get your rolling 3 month average.

This is depending on how you want to calculate the rolling three months. Is that the current month and the prior 2 months or the prior 3 months excluding the current month? If you want the prior 3 months just calculate the lag for 3 months ago and add that value instead of the current value.

If you're interested here's the writeup using a beast mode (but if your rolling average won't need to have filters dynamically applied to it I'd recommend keeping it in the magic etl): https://dojo.domo.com/discussion/52679/domo-ideas-conference-beast-modes-rolling-averages#latest

Hi @JR_S1L

You'd need to keep your data as is to partition based on name to get the lag for the prior 3 months to get your rolling average. You can use a rank & window tile in Magic ETL to calculate the lag just make sure you're partitioning by the name and that you're sorting/ordering by the month

I don't know of a great solution here if you are only able to use Quick Filters. You could build a beastmode but that isn't great.

CASE WHEN `datapointA` = 'green' OR `datapointB` = 'circle' THEN 'Yes' ELSE 'No' END

The issue with this is you would need a separate beastmode for each combination of ORs.

Something else you might try is combining the columns together.

CONCAT(`datapointA`,'|',`datapointB`)

This would give you a list of all unique ORs you might want. This issue here is you would need to select multiple values in the Quick Filter but this would allow you to pick any values in either column.

Your quick filter might look something like this.

blue|circle

green|circle

green|square

orange|square

etc

Then you would select all values with 'green' in the first half and all options with 'circle' in the second half. If you go this route I'd recommend ensuring there are no NULL values in these fields as any NULL value in a CONCAT causes the whole thing to return NULL.

CONCAT(IFNULL(NULLIF(`datapointA`,''),'N/A'),'|',IFNULL(NULLIF(`datapointB`,''),'N/A'))

This would turn any blank or NULL values in either column into 'N/A'.