We are looking for a way to monitor the last run of our email datasets. At the moment we are feeding the data through Email either manually send to Domo or via Obiee, however from time to time either someone forgets to upload the data or the automatic upload fails. As a solution, we would like to build a dashboard containing cards to monitor the state of the most important datasets in terms of when was the last upload. I was thinking for beast mode formula comparing the last run ( column is a timestamp) and today, however, for some reason it's not working as it should be with the following example:
(case when DATEDIFF(CURRENT_DATE(), MAX(`_BATCH_LAST_RUN_`)) <= 2 THEN 'true' ELSE 'false' end)
In my head, this should give us "true" if the dataset has been updated in the last 2 days and "false" if it hasn't been updated.
My guess is that there is some kind of problem between current date, _BATCH_LAST_RUN_ ( which is "timestamp" ) and the "2".
I would really appreciate if someone can help me to figure this out.
1) Take out aggregation and just pump your results into a table card.
2) break your beast mode into smaller beast modes.
what is the result of datediff(current_date() , batchLastRun) ? for each row, is it returning the expected result?
with visual inspection, are the values comparable to 2 (i.e. is it consistently returning a row for each record, and is that value numeric?
case when DATEDIFF(CURRENT_DATE(), MAX(`_BATCH_LAST_RUN_`)) <= 2 THEN 'true' ELSE 'false' end
When something ins't working. What is the perceived problem? Does the beast mode not calculate? Are you getting the same value for every record? Is the value being returned different from what you're expecting? Which dataset are you even using??
It sounds pedantic, but if you can't articulate with specificity what the problem is, it's going to be impossible to prescribe a solution, especially b/c you didn't really give us a screenshot of your current results to work with.