Best Of
Re: Query Based on a Value Between Two Date Columns
You could use a variable that would allow the user to select a date. You would then create a beast mode that creates the open/closed logic. Something like this:
CASE when variablefield >= create_date and varialbefield <= last_update then 'Open'
ELSE 'Closed'
END
You would then put this beast mode in your filters section and either filter to open, or make it a quick filter and allow people to choose which is open or closed. Or just drag it in as another field on your card.
Re: Query Based on a Value Between Two Date Columns
you can use the date that they select with the variable and use some functions in your beast mode to apply to the entire month.
This would get you the first day of the month:
DATE_SUB(`dt`, INTERVAL (DAYOFMONTH(`dt`) - 1) DAY)
This would get you the last day of the month
LAST_DAY(`dt`)
You can use these functions while comparing to your create and last updated dates.
Re: Facebook Advanced Ads Connector not returning Conversion Values?
The conversions you are looking for are most likely here:
actions: The total number of actions people took that are attributed to your ads. Actions may include engagement, clicks or conversions.
action_values: The total value of all conversions attributed to your ads.
Re: More Granularity in the 'Manage' Role Grants
@PWeed yes! I have also run into this issue when trying to ensure my SOX compliance. I have been asking for more granularity on a ton of roles and would love to see this!
Ashleigh
Re: how to aggregate nested average case statement
sum(count(distinct case when AVG( SignUps ) >= AVG(`Target`) then StoreNumber end)) fixed (by Region )
/
sum(count(distinct StoreNumber )) FIXED (by Region )
Re: Exporting Mega Tables
@Ashleigh
The limits for exporting from Mega Table to Excel are listed in this KB article:
"When exporting to Excel, only data that is displayed in the Card will be exported, up to 1M cells of data while exporting to CSV will output all rows. For Megatables, Pivot tables, and HTML tables, this is 50k rows by default and up to 100k rows by manually increasing the row limit inside of Analyzer."
Your Analyzer will likely say "All Rows", but if you change it to 100,000 then you will get 100,000 rows in your Excel export.
I hope this helps!
CKunz
Re: datediff at row levels
@zuchu
You should be able to do this using the Pivot tile in Magic ETL.
You can find more information in our Knowledge Base article:
Basically, you can pivot the rows based on their order number, which will create new columns for each order. You can then use the DATEDIFF formula with Order 1, Order 2.
Here are a few screenshots that walks through those steps with very simple dummy data:
Starting Data:
Pivot Tile:
Output Data:
Hope this answers your question!
CKunz
Re: Bulk User Email Update
This question is resolved. During the bulk upload you have to create a "newEmail" column. In the 'email' column enter the the current email and in the 'newEmail' column enter the new email address to which you want to change.
Re: card design troubleshoot
@Jbrorby You can achieve this using a Stacked Bar + Line chart without the Trellis Settings. In order to stack the bars, you will need to create two separate beast modes for True and False values:
For true:
sum(case when test = 'True' then lte_ibd_transaction.net_shipment_count else 0 end)
For false:
sum(case when test = 'False' then lte_ibd_transaction.net_shipment_count else 0 end)
Then you can place both of these fields in the "Series" spot in Analyzer. In order to show the table below the graph like in your Excel version, check the box under Chart Properties > Data Table > Show Data Table
Re: DOMO Conditional Formatting in Tables (Change of text color only to an RGB value, not the full cell)
If you want the end result to look like this:
Then you can use an HTML table and create a beast mode that looks like this:
