Is there a way to create a static temp table in Beast Modes?

adeshon · 2022-09-12T14:43:00+00:00

There was an error rendering this rich post.

I'm the MajorDomo for a large retailer, and our Fiscal Calendar uses a 4-4-5 format. This means we have a Week 53 this year, and a large portion of our Domo reports are built off of a TY/LY comparison. I'm working on logic to reconfigure our reports always look 364 days or 52 weeks ago, rather than look at the same Fiscal Week the prior year.

To do this, I pulled in a "Week Number in Epoch" field onto one of our datasets that incrementally counts from the first week of 2010. The idea here was that was that I would write a Beast Mode to find the "Week Number in Epoch" value for each record, then subtract 52 weeks from value, and pull the "Net Sales" to get LY.

The problem I ran into is that any Beast Mode logic needs to compare the Epoch columns against itself, and this breaks apart quickly, as the "Week Number in Epoch" column will never equal itself minus 52. The closest I've come was using Fixed functions to always pull LY WTD, but this logic breaks apart if the user needs to look at a different Fiscal Week.

CASE WHEN `WEEK_NUMBER_IN_EPOCH` = (MAX(CASE WHEN `DATE_VALUE` = CURDATE() - 1 THEN `WEEK_NUMBER_IN_EPOCH` END) FIXED(FILTER NONE)) - 52 THEN `NET_SALES` END

This logic could work if Beast Modes could read that second CASE statement as a Temp Table, rather than rendering it across each record. For example, last week's "Week Number in Epoch" could be 1174, then the Beast Mode would isolate that value, and search other records in the same column. I should be able to accomplish something similar by adding "LY Week Number in Epoch" columns and searching for records where "LY Week Number in Epoch" = "Week Number in Epoch", but for the sake of performance, I would rather do this in one column in possible.

Does anyone know if Beast Modes have this sort of functionality? Or has anyone else struggled with Week 53 logic and found a better solution? I'm open to input. Thank you in advance for your time!

Find more posts tagged with

Date

Filters

Table

Beast Mode

Accepted answers

All comments

MichelleH

@adeshon Do you have a fiscal calendar enabled in your Domo instance? Your fiscal calendar is in essence the temp table you are describing, which can group and filter your data based on your own company calendar. Here is a KB article with more information: https://domohelp.domo.com/hc/en-us/articles/360042924254-Using-a-Fiscal-Calendar

Note that this only impacts card Date Ranges and filters, so beast mode date functions cannot see the fiscal calendar. You may need to map fiscal weeks/periods/years into your dataset using a dataflow or view to make beast mode comparisons.

adeshon

I do have a Fiscal Calendar enabled. The issue I'm running into is that Beast Modes have no integration with the Fiscal Calendar. Several of the cards in our instance use Beast Modes to return a TY/LY comp side-by-side. Here's an example of a current LY WTD Beast Mode that I'm trying to replace with an Epoch calculation:

SUM((CASE WHEN (((`FISCAL_YEAR` = `CURRENT_FISCAL_YEAR` - 1) AND (`FISCAL_PERIOD` = `CURRENT_FISCAL_PERIOD`)) AND (`WEEK_NUMBER_IN_FISCAL_YEAR` = `CURRENT_FISCAL_WEEK`)) THEN `SALES` END))

If Beast Modes could read my Fiscal Calendar, that would solve my issue, but as is, I need to somehow write a Beast Mode to always return records of sales where the value in one column equals that same column minus 52. I think I need to pull in a second column for Week in Epoch - 52 in order to compare values that way, but I was hoping there would be a solution that would let me do this using one single column.

Jones01

@adeshon we have run into the exact same problem. We have the fiscal calendar enabled and while it is great for breaking charts into fiscal periods and for choosing fiscal periods on the date selector the logic on the period over period charts for prior periods doesn't match our clients.

Our clients take the date this year -364 days to get a comparable date. Domo matches based on the year and week number. This also causes a problem when looking at a 53 week in isolation or ytd including week 53 as it will never have anything to compare to.

For the time being we have an etl that allows us to grab last year and previous years from a beast mode.

https://dojo.domo.com/main/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons

I'll add the idea to the dojo so make sure you give it a vote. 👍