Best Of
Re: I need to make a bar chart that combines multiple columns based on date
And you could create a bar chart such as…
quartername:
CASE
WHEN QUARTER(date) = 1 then 'Quarter1'
WHEN QUARTER(date) = 2 then 'Quarter2'
WHEN QUARTER(date) = 3 then 'Quarter3'
WHEN QUARTER(date) = 4 then 'Quarter4'
END
Expand on the chart by setting filters with your selection of year. Or include year as part of the chart. You can make a dropdown for year by making a second card, and creating a filter chart type - dropdown. Or simply drop your year formula onto the filters and pick which year you want.
Re: I need to make a bar chart that combines multiple columns based on date
@KristinDavis I agree with @ArborRose about the format of the data. To get it into that format, I suggest using a Dynamic Unpivot tile in MagicETL to add Program as its own column. Then you should be able to create a card off of that output dataset with Date in your X axis, count/distinct count of user in the Y axis, and Program as the series.
Re: YoY change beastmode at card level in table chart
You can create formulas for each year. That way its dynamic and you don't have to hard code the years. For year to date use the following. Take out the condition that says and date less than equal current date if you want the full year. If your data field is amount, simply use [amount] and add the topics and it will break on the topics.
Current Year:
sum(
case when YEAR(date) = YEAR(CURRENT_DATE())
and date <= CURRENT_DATE()
then {yourdatafield} else 0 end
)
Previous Year:
sum(
case when YEAR(date) = YEAR(DATE_ADD(CURRENT_DATE(),-365))
and date <= DATE_ADD(CURRENT_DATE(),-365)
then {yourdatafield} else 0 end
)
Difference:
sum(
case when YEAR(date) = YEAR(CURRENT_DATE())
then {yourdatafield} else 0 end
)-
sum(
case when YEAR(date) = YEAR(DATE_ADD(CURRENT_DATE(),-365))
then {yourdatafield} else 0 end
)
Re: Different time zone
Hi @MichelleH,
Just for the record, if anyone faces the same situation as me, I solved using two functions.
By relating New_York and Sao_Paulo it solved the time zone problem but it didn't bring up the correct time, so I just adjusted it by adding the time difference in seconds.
Re: Limiting access when row count is low
You can look into using a FIXED function to get the overall total.
CASE WHEN SUM(COUNT(DISTINCT `ID`) FIXED ()) > 10 THEN 1 ELSE 0 END
Re: Date Formatting in Tables/Charts
Hi @SamanthaC, it depends on the card you're using. Two things might control how the dates are displayed.
- If you're using that date field to graph by it, then check the Graph By option and make sure you have it at the granularity you want.
- If this is just a column on a table or similar, you can control how to format it, from the menu on the field, selecting Format and then Date and how to present it.
With the last one you can show only a part of the date, like only the month or the like.
Hope this helps.
Re: BEAST MODE CALCULATION ISSUE
I find that Beast Modes often fail if the top-most level of the calculation is not an aggregation, but aggregation functions are used elsewhere in the Beast Mode. What I mean is, in your calculation, you are using SUM several times, but the top-level of the Beast Mode is a CASE statement and a divide operation. This may seem unrelated to your ABS problem, but I find that sometimes Beast Modes function unexpectedly like that when the real root cause is something like this. I would try remaking your Beast Mode so that it is all encased in a SUM. You can either achieve this by finding a way to remove all aggregate functions underneath that SUM, because Beast Modes don't support double aggregations in that way, or explore using FIXED functions to achieve double aggregations, still wrapping the Beast Mode in a SUM (or other appropriate aggregation).
Here is some great documentation of FIXED functions in Beast Modes if you aren't familiar:
brycec
Re: Summary Number value type
In Analyzer, click on the Summary Number and click on Show Formatting Options below the label. Choose Number (or Currency) and uncheck abbreviate and check use thousands and set decimals to 2.
