I've just discovered that cards will use the workplace setting for a timezone, [e.g. AEST] whereas MySQL Dataflows will use GMT.
I'm trying to sum total sales by month, to then apply a monthly commission formula.
This sort of structure:
|Month | Sales | Commission|
|2017-09|10,000|3,000|
|2017-10|12,000|4,000|
Where Month = DATE_FORMAT(`completedAt_$date`,'%Y-%m')
and Sales = SUM(`Amount`)
I need to use MySQL rather than beastmode calculations in a card, because ultimately I'll need to sum it down to a single number and use that figure in other calculations.
What I've discovered is that if I view the data used by my card and SQL on an individual transaction level, it's identical [ID, Amount] apart from the 'Month' field, which will vary on the boundary between months where, say, the transaction in AEST is in Nov, but in GMT is Oct. This creates substantial disparities in my data!!!
I'm unsure if this is by design or a Domo bug ~ any suggestions on how to automatically have mySQL use the workplace timezone rather than GMT?
