Best Of
Re: Period over Period (MOM/YOY) option in Multi-Value card for 2 months prior
@NathanDorsch glad I was able to help. If you can accept any answers that helped you, that will help others in the community.
Re: Unique ID within parameter
You could utilize the Lag function in the rank & window tile get the previous row's flag next to the current row's flag as another column. Then use the formula tile to overwrite the unique id if the previous and current flag are the same. Finally, you could use the rank & window again and use the row number function and partition on the account number field.
Hopefully I understood your request correctly and that this helps.
Re: Unique ID within parameter
@user027926 you can't use a lag function in a formula tile within Magic ETL. That is why I suggested doing a lag first with the rank & window tile to put the previous value next to your current value as a new column. Once you have the previous value in the same row as your current value, you could do the formula work that you want to do.
I'm not sure if it will help (and I tend to stay away from using them regularly) but you can do window functions at the card level. It is not enabled by default, so you need to as your CSM to "enable window functions in beast modes." You are wanting to do quite a few things, so I don't know that you can do it all at the card level, but thought I would mention it in case it is helpful to you.
Re: Card Drill
@Fadem I have not had trouble saving my drill paths and the columns in my table card staying the way I designed them. Are you clicking on the wrench of the main card and choosing Edit Drill Path and then clicking Edit View and then designing your card (I assume a table card) the way you like and then choose Save and Close? That should be all that you need to do.
The final dataset table view can only be shown or hidden and all the columns in the dataset are shown.
Hope this helps.
Re: trends ratio for last 4 months
If you do have a date field, you can normalize a couple different ways to get them to be grouped by month and year. The easiest is probably using the Last_Day() function, like this (replacing dt with your fieldname that has the date:
LAST_DAY(`dt`)
This will create a date field for you and have all the transactions in a given month have the same date. You can then use this field in your rank and window and order by this field descending. Try that for starters before tackling the rank and see how it looks for you.
Re: Calculated Field - Outstanding Invoice Count with date constraints
That formula won't validate as constructed. I would re-write it like this, assuming you have a due date field:
SUM(case when `type` IS NULL and DATEDIFF(`duedate`,CURRENT_DATE()) <=14 then 1 else 0 end)
This assumes type is actually null and not an empty string. I am using sum instead of count because we need to only count certain ones and we are using ones and zeros to do that. I'm assuming the due date is beyond the current date and so we are looking to see if it is within 14 days.
Hope this makes sense.
Re: window.addEventListener("message") does not trigger when card is clicked
Hi @xiaodongliang, how did you go with this? What did Support say?
amehdad
Day on Day Change Issue
Hi,
I am trying to calculate the day on day changes of campaign activities (i.e. Sends, Receipts). The problem experienced is restricting the unique counts to one day because the code I've been using includes some records of the previous day. Depending on the time of viewing the domo cards, it takes the record of 24hrs from the time of viewing (which is not necessarily 24 hrs of the entire day). I've been using date_format, specifically:
(COUNT(DISTINCT
CASE
WHEN DATE_FORMAT(`campaign_schedule_date_local`, '%Y%m%d') = DATE_FORMAT(DATE_SUB(CURDATE('%Y%m%d'), INTERVAL 0 Day),'%y%m%d') AND `media_booking_type` <> 'Sample' AND (`campaign_status` = 'Scheduled' OR `campaign_status` = 'Sent')
THEN `campaign_id`
END))
I want to see if using time_format is able to restrict the timeframe for representing daily changes to the exact day (i.e. Up until 23:59:59) but have not been able to see any values return from the code when changing from date_format to time_format. Could you please advise?
Any help is appreciated. Cheers
amehdad
Re: Can someone help me with a beast mode to fix this? I would like it to start at 12a and to end at 11p
@GrantSmith by a millisecond .... again. :)

