marcel_luthi Coach

Agree with @nmizzell, there are several ways to do this. The first thing I'd do is in ETL break the revision number into 2 separate columns, so I know which is the original vs the revisions. A formula tile with 2 formulas (for OriginalID and RevisionNum) would work. OriginalID CASE WHEN `Revision` IS NULL THEN `ID` ELSE…

As @ColemenWilson suggested, once you have the year as a variable, you can make your cards show all time, and then create a Beast Mode to use as a filter instead, which would look something like: CASE WHEN YEAR(`date_field`) = `variable_name` THEN 'show' ELSE 'hide' END And make it so that you filter only to the show…

Do you need it to be a single card? If so you'll need to configure a really custom brick (which with HTML knowledge and some CSS you should be able to achieve). If that is not a requirement, you could use multiple single Value Cards to emulate the layout when placing them in a dashboard (you might not get to have the lines…

While not as visually pleasing, if the break of the lists always go between specific % values, you could use the Scale marker property to highlight the middle section, and leave with no color the sections above and below this range, and just hope people understand that things below that section are good and outside of it…

Have you tried using BY Sitename to see if it works? FIXED will generally perform a double aggregation, one at the level you specify in the BY clause and then the surrounding one at whatever level you're displaying in the card. If the goal is to have a card in a dashboard that only shows this, then you could instead…

Yes, a Lag function of 1 (Window and Rank Tile), partitioned by customer and sorted by date ASC, should be able to add next to each row the date of the previous entry, so your example would become: id | status | date | previous date 1 | disconnect | april 4 | 1 | reconnect | april 7 | april 4 2 | disconnect | june 4 | From…

Depending on what you need, ETL is most likely the way to go. Otherwise, if what you need is a list of the last N workable days, you can do that with just a table, filters and limiting the number of rows.

The above solution works great, as long as your card/dashboard is not meant to be responsive to user filters and you have clearly defined partitions over which the rank function should be applied. If your top 20 is meant to be responsive at the view level, I usually show a Horizontal Bar char and limit the number of bars…

It'd depend on what Monthly basis means to you, and what the Amount should be. If you want the opportunity to be counted on each month it was open (as of the end of the month), I'd suggest doing a ETL to Join your table with the DOMO Calendar one in a FULL OUTER join fashion (only joining the EoM dates), and then filter…

You can do this with ETL, but since your Score in Table 1 is daily and you want to show it monthly, you'll need to decide how that should be handled, would if be an average of the average (which is not often recommended), the max score in the period or the last score for the month, depending on this, the way to build the…

I guess you're doing this in ETL, can you debug and see if before the tile where COALESCE is being applied, the tile where the CASE function is being applied is actually returning any values? If so, as you're not doing multiple, can you try replacing COALESCE for IFNULL, a bit less flexible but a function that has been…

How have you configured your Rank and Window tile? You should be able to do: Function of LAG 1 on the Hours Order by Date ASC Partition by Engine and Facility As @ColemenWilson suggested, unless there are other columns in your structure that could cause multiple entries for each month.

First you shouldn't partition on Snapshot Date, you should partition by CSO_Number, as you care about how each Order has changed over time between snapshots, and your Order by should be the Snapshot Date Ascending, so you'll always get the value for that Order on the previous day. Not sure where the SNAPSHOT_DT_FOR_LAG is…

Is your top level card a Pivot Table? Where are you clicking exactly to drill down? Do you have any filters on the top level card that come from Beast Modes rather than actual values from the dataset? These things can all affect how drilling works and yield different results. Something else that has often caused me…

If you're looking at total changes in the given period there are a couple of questions on how you'd address the same record being affected on during the window, examples, assuming Oct 1st as the selected date: An order gets created on 10/1/2023 with a ship date of 10/7/2023 and on 10/6/2023 the ship date gets updated to…

The same can be achieved with a single Dynamic Unpivot btw (I always forget this one is there).

As @GrantSmith suggest ETL will need to happen first, something like this should work: The Constants just identify the column you want to keep, while the alter renames it and removes the other two. This produces an output which then you can pivot the way you want. Keep in mind that for Month Year to be sorted properly, you…

You can achieve a similar behavior with a Pivot Table, but PY, FY24 and B will be displayed as Columns not Rows. Sadly there is no current way to indicate DOMO to display the values as Rows as opposed to columns (Ideas Exchange submission so you can upvote). You could most likely achieve this if you do transform it with…

If the rolling total always goes up (like you don't have centers discontinuing the product or closing) then the max alternative will work just fine. Should this not be the case, my approach would be to use an ETL to obtain the daily change and have that one as an additional column (the only problem you'll have is that on…

Congrats @GrantSmith and yeah, I hope there is a badge to commemorate this achievement!

This would all depend on what you need to show and there are 2 different ways this can be approached. Using a variable This approach works when you want your users to enter a single date and then you just return the count by creating a beast mode that filter the contracts that are active on that date by comparing the…

How do you know which record the A0 - Request Received and the A3 - Ready for IT PB belongs to, in your example you have 2 A0 - Request Received entries, also can a record have more than one of each of these entries, and if so, which one should be used (this is why @GrantSmith suggesting wrapping the field in a MAX…

@PJG just keep in mind that it'll set that aggregation at all levels, not only for the total/subtotal. Up to a certain point if different aggregation levels are needed, you'll need to define a Beast Mode to achieve that. Example, if you are showing total sales by user (SUM), but want the Total of the table to be the…

As a best practice I try to create separate beast modes for the numerator and denominator, and see if those are returning the values I'd expect, so I can easily troubleshoot from there to see whether there are flaws in the logic or where are things going eerie. Since all of them go to 0, it's likely the numerator is…

@HCyamada even if called Tooltip, you can actually use those and display them right on the card but using it as one of the fields in the text options. As for the it to work, you'll need to tell the FIXED function which filters are allowed, so it will understand you don't want to compare against to global total but totals…

You could use a beast mode and and a Tooltip to show the data you want (given that you want to show it the same way you currently have it, in a bar chart) but this will compare all of the entries against the one you want. Your beast mode would look something like: (SUM(`Volume`)/SUM(SUM(CASE WHEN `Period` = '2022A' THEN…

Where is this query being used? In general the syntax looks odd, if you care about the specific invoice numbers the general MySQL query would be: SELECT * FROM CHARGE_DETAIL WHERE INVOICE_NUMBER IN (217653436, 217668183, 819802440, 820092602) Does this helps?

With the default aggregations this won't be possible but you could achieve this behavior by using a beast mode instead. Here's an example where the first column is done by using AVG as the aggregation, while the second is done using the beast mode. Keep in mind that this works fine with a 2 level row aggregation and you…

Agree having a way to specify Filter logic would be a great advantage rather than having to build lots of custom beast modes just to select how logic should apply.

Try breaking down your problem into smaller portions to see where it's failing, create 2 separate beast modes and display them in your table, one for the portion that the FIXED function is calculated, and another for the other one and see if each one is returning what you expect or not.…