Sean_Tully Contributor

It's something like this: sum(SUM(Total LTV) fixed (by Year, Dimension 2, Dimension 3)) / sum(SUM(Total Orders) fixed (by Year, Dimension 2, Dimension 3))

I think this would work conceptually: In ETL, filter out all the zeroes, so you only have the rows with the calculation values. Then, use a window function to lead the next date with a value, so that each row has the original date, the calc value, and the next date with a value. Join that back to the original input with…

How is the dataset you're using structured? And can you clarify your first sentence? I'm not sure what "and not update at" means.

Try just sum, not sum(DISTINCT), if you have 2+ rows with the same value sum(DISTINCT) would only use one of them in the calc.

I also usually Google "domo" plus whatever I'm trying to find/solve for, I generally end up here.

I think if you create the right PDP policies, you can prevent this type of sharing from happening: https://domo-support.domo.com/s/topic/0TO5w000000Zao2GAC/personalized-data-permissions-pdp?language=en_US

Yes, that's my understanding based on the thread I linked to. We usually reach out to our account rep instead of support for these types of things, though.

Looks like it's something that can be turned on behind the scenes: https://community-forums.domo.com/main/discussion/55399/scheduled-report-excel-attachement

I think you can do this using a Grouped Bar chart. The x-axis would be locations, and you'll have to create beast modes for the 'This year' and '1 year ago' metrics. When you plot them next to each other, you'll get the viz you want. It's just a little more work than the out-of-the-box YOY chart.

Not exactly the same, but you can add rules to the Colors chart properties to shade the cell red. I don't think that you can do the font color there, though. If you uncheck "Apply format to table row" it will only shade the cell, so you will get the same effect (highlighting the negatives), but via shade rather than font…

For MTD and WTD, you can use the dayofmonth() and dayofweek() functions to get the numerical value of the previous day in terms of week or month, and then compare to last year's to make sure the same value is less than or equal. QTD and YTD can get tricky depending on if you're using a special fiscal year or not. If it's…

You will have to either build out beast modes for each metric, or build them out as formulas in Magic ETL. In essence, you'll be making a bunch of formulas that look like the one below, which would be for sales, previous day, this year: sum(case when date_field = current_date() - 1 then sales_field end) Sales, previous…

Maybe something like: sum(ifnull(`R-Phone Interview`, 0) + ifnull(`R-Virtual Interview`, 0))/(count(`R-Phone Interview`) + count(R-Virtual Interview))

I don't have a solution at the moment, but I think the solution will depend on how your data is structured, if there are records for every date, and how you want to handle when a week straddles two different months.

I would probably do this in Magic ETL. You can use a window function tile to lead the next day's tank level, which would allow you to do the math in your screenshot. I think you can also use lead functions in Beast Modes but I haven't used them there as much.

This solution worked on one of my datasets, might be worth a shot if there is a wide set of letters that could be in your field. Otherwise, @ColemenWilson and @GrantSmith's suggestions will do the job. I created this beast mode: case when length(`field`) = length(`field` * 1) then `keep' else 'ignore' end Multiplying the…

Hopefully someone knows an easy way to do this, but the only way I know how to do this would be through ETL by cross joining a set of dates against the project datasets. The flexible joins in ETL help a little bit - the join would be something like custom_date >= date_opened and custom_date < date_closed. Going to track…

I think David's reply is correct if you don't have a rank field yet. If the rank field already exists, you instead would have to group by the id to find the max(rank), and inner join that back to the data, with the join fields being id = id and rank = max(rank).

There may be an easier way, but I usually write a beast mode to assign a number to each string value, like: case when engine = 'Engine 1' then 1 when engine = 'Engine 2' then 2 when engine = 'Plant Engine 1' then 3….etc. This may not be ideal if the values of the string change or keep growing over time. If the number is…

I think you'll end up with a couple of different datasets here: The email connector dataset, which appends the new data from each email A dataset where you run the full email connector dataset through Magic ETL. Here, you would find the max batch date for each month, and then isolate those rows before outputting to the…

It depends on how your data is structured, but one solution might be to create a Beast Mode where you concatenate the three values together, such as: concat('Current - ', current_field, ' Previous - ', previous_field, ' Next - ', next_field)

I think david's point about the beast mode lacking aggregation is the issue. It should probably look like this: sum(Visits Agreed Upon-Billable activities)/sum(Visits Agreed Upon)

If you want the percentages in the same table as the Cust_ID rows, you'll need to use a FIXED function (https://domo-support.domo.com/s/article/4408174643607?language=en_US). If not, I would create a new card without the Cust_ID rows so that you only show Sale_Type, and then use a Beast Mode or format one of the…

It would help to know more about where you are entering this formula. The DATEDIFF(…) part should work as a Beast Mode or a Magic ETL formula, you don't need the "AS DaysTillDue" part in the code since you can enter "DaysTIillDue" as the column name. Also, make sure the ShipDate has two tick marks around it, not single…

No problem, and good luck! I haven't had to find a median date previously, so I think there are some details I didn't think of, like the total being an odd number, and therefore the total/2 not being whole. Not sure what the best way to handle that would be - my first thought was rounding, but I'm not sure if that would…

This is a fun one! Here's how I might find the median date in ETL: I'd use a row number function, partitioning by market and sorting by date, to get the data in order and count the data row-by-row. I'd also have a separate group by, by market, where I'm getting a total count of records and dividing by two. Next, I'd join…

I would probably create a Beast Mode to find the '12 months ago' data, and use that as a filter on my card so that I'm only showing that data. I don't know the fields in your data, but it would probably look like: case when date_sub(date_format(current_date(), '%Y-%m-01'), interval 12 month) = date_format(your_date_field,…

I haven't used Magic ETL upserts yet, but from other experience this sounds like your upsert key isn't unique to each row. Hopefully someone else who has used this feature can give you a more certain answer.

I interpreted that part of the video as meaning that he has two different cards, each with a different source of data, next to each other on the same page. You can't really "combine" cards.

I think if you wrap the field in UPPER, and the string pattern after LIKE is uppercase, this should accomplish what you want. Works for LOWER and lowercase, too.