Best Of
Re: Period Over Period Calculation Challenges
@RocketMichael Is your dataset organized at day grain like your table? Or are you doing the aggregation in your card? If it's in your card, you're going to need to throw SUM around all your case when statements:
(SUM(CASE when `Period Type`='Current' then `Shipped CoGS`end) - SUM(CASE when `Period Type`= 'Last Month' then `Shipped CoGS` end)) / SUM(CASE when `Period Type`='Last Month' then `Shipped CoGS`end)
Re: Is there a way to make column headings on the DOMO table cards dynamic?
You could consider using the transpose property on the mega table, which essentially pivots your data without writing beast modes. It would turn something like this:
Into this:
Re: How to build a filter for only the most recent value of a column in a data set to show.
only addendum I'd add to @MichelleH 's post would be to not filter out rows. just add your row_number for nth-most-recent visit.
in one card you can filter on "nth-most-recent-visit = 1" and on the other card just filter by patient.
Re: How to build a filter for only the most recent value of a column in a data set to show.
Hi @slewis1 I would suggest using a Rank and Window tile in the ETL to add a row number column, ordered by visit date (descending) and partitioned by patient record number. You can then filter to rows where this row number equals 1 to identify the most recent visit.
More on the Rank and Window tile: https://domohelp.domo.com/hc/en-us/articles/360044876094-Magic-ETL-Tiles-Aggregate#3.
Re: How do you change the order date "2020-01-06T16:56:00" to the first day of the month?
Hi @gbennett
If you have the ETL 2.0 beta you can utilize a formula tile and write a formula like (It'd be the same if you want to do it as a beast mode):
DATE(`Date`) - INTERVAL (DAYOFMONTH(`Date`)) DAY + INTERVAL 1 DAY
Essentially you're subtracting the day of the month from your date and adding 1 day to get the first of the month.
Re: Ignore Row Context / Power BI 'ALL()' Equivalent?
@chapman Domo recently rolled out the FIXED function which should accomplish what you're after. You can write a FIXED function that will ignore filters applied to your card.
https://domohelp.domo.com/hc/en-us/articles/4408174643607-Beast-Mode-FIXED-Functions
Re: Connector Deprecation Announcement - Amazon Marketplace Web Service (MWS)
We are definitely taking other steps to notify clients as well but wanted to make sure the information is out there.
Tclark07
Re: How to remove columns with Null values?
You could try using a Dynamic Unpivot Tile, then a filter tile to remove any rows with a null value and then pivot the remaining data again.
You could also look into creating a dataset view of the data. When you are looking at the "Overview" for a dataset, you will see a section called "Data Profiler". This section will provide you with some recommended actions:
If you click on "VIEW ISSUE", you can create a view of your dataset that will remove any columns that are null:
Then just use this view as the input for your ETL.
The advantage to using the unpivot/pivot approach would be that it would be dynamic, meaning that if data did come through in one of the fields, the field would no longer be deleted. However, once you remove the field using the dataset view, then that view will never contain the field.
