Best Of
Re: "current year, current month" and "previous year, current month" Beast Modes
You can multiply amount by 1 to force it to be a number or change the data type in an ETL
Re: "current year, current month" and "previous year, current month" Beast Modes
@damen Current month:
case when YEAR('Transaction Date') = YEAR(CURDATE()) and MONTH('Transaction Date') = MONTH(CURDATE()) then 'amount' end
Previous year, current month (assuming this means same month last year):
case when YEAR('Transaction Date') = YEAR(CURDATE()) -1 and MONTH('Transaction Date') = MONTH(CURDATE()) then 'Amount' end
Re: Vertical bar with 2 or more series
@Fabien What question are you trying to answer with this card? Based on the number of owners visible in your screenshot, adding an additional series for request type would likely make the card very difficult to read and insights would not be obvious to whoever is looking at it. Have you considered adding Request Type as a quick filter instead?
As a side note, I recommend checking out this KB article on best practices for selecting chart types: https://domohelp.domo.com/hc/en-us/articles/360042935294-Best-Practices-for-Choosing-Chart-Types
Re: SQL Flow taking hours to run
This will run faster is you utilize indexing in MySQL (help article), which would require a transform for each of your tables to create two new columns for the month extracts you are joining on...e.g. LDGRDATE_Month and LDGRDATE_PriorMonth. Once those columns are created, you can add an index to them prior to joining them in your statement above, which should improve runtime.
I would also recommend utilizing Magic ETL 2 and using the formula tile to extract the month parts and then joining on those columns, this would likely be the most optimal runtime.
Re: Date range selector
Hi @Jones01 I'd recommend either of these options:
- Use a field for first day of week or first day of month in your range selector card
- Use the dashboard global date picker to filter your dashboard instead
Re: Why is my card grouped by month in the table but not in the chart, and how can I fix it?
@louisshh What do your sorts look like on the card? If you're sorting on your y-axis columns without doing an aggregation in the sort, you can get multiple lines.
Re: Magic ETL Help
@damen Since it's a practical test, I won't give the answer, but I will give a hint. Look into the Rand & Window tile.
https://domohelp.domo.com/hc/en-us/articles/360042922814-Old-Magic-ETL-Tiles-Rank-and-Window
Re: Magic ETL Help
@damen In the Rank & Window tile, use the LAG function, ordering by transaction date and partitioning by customer. The LAG function looks at the nth previous row. So if you did a lag of 1, it would look at the previous row and the column would now provide you with the previous date, and you can do a formula to calculate the difference between the dates.
Re: Magic ETL Help
@damen After adding the column that calculates the days between orders, you can do a Group By tile to group the customers together and average the days between orders.
Re: Is there a way to auto-delete the data after a certain period of time?
Hi @yli_2596, I am assuming you are referring to the data in the underlying architecture, and for that, there's currently not an easy way to delete data. A few options you can explore are:
- Creating a dataflow to input your dataset, filter out the rows you don't want and then write it out to another dataset,
- Temporarily using the replace method and then go back to your append method (using the ETL tiles),
- Requesting certain rows to be deleted via Domo Support
amehdad