Best Of
Re: How do you perform a calculation within a pivot table?
You can do a conditional beast mode to calculate your revenue or cost calculations and not use the type as a grouping
Revenue
CASE WHEN `Description` = 'Revenue' THEN `Actuals` ELSE 0 END
Cost
CASE WHEN `Description` <> 'Revenue' THEN `Actuals` ELSE 0 END
Profit
CASE WHEN `Description` = 'Revenue' THEN `Actuals` ELSE 0 END - CASE WHEN `Description` <> 'Revenue' THEN `Actuals` ELSE 0 END
Re: Global date range filter in dashboard, have it compare to exactly a year ago in guage card type.
Hey @jakebutterfield
I recommend restructuring your data model so you can have multiple periods for each date (in your case this year and last year). I've done a write up on this in the past which might be helpful for you.
This way they can select whatever date range they want and see the selected. value and the value from last year.
Re: WEEKOFYEAR() should be removed from the approved function list or it's behavior should be changed
Thanks for bringing this up Zac. We've updated the in-place Magic ETL documentation for the WEEKOFYEAR() function. The new docs will mention the discrepency with MySQL and recommend that WEEK() always be used over WEEKOFYEAR() in the current version of Magic ETL. We can't immediately change the existing behavior of WEEKOFYEAR(), of course, but we're looking into ways we might do it eventually. We don't try to guarantee perfect behavioral parity with MySQL, but we do try to avoid gotchas like this one.
rco
Re: Slicers
There actually is a way to allow filtering from one filter but not another one.
To do this, go to: Edit Dashboard > Edit Content (on your filter slicer) > Change Interaction > Apply to selected Cards.
See below:
Re: Slicers
You can disable filtering entirely on a card by card basis on design/story dashboards. Edit the dashboard > edit content on a card > change filter exceptions > uncheck allow filtering. This unfortunately disables any kind of filtering for that card, not just your slicer. There is no way to allow filtering from one filter but not another one.
brycec
Re: Weighted Average Calculation
For this you'll need to get the Value of the rate rather than the %, and then calculate the new percentage as the ratio between the two.
At the row level you'll need to have a value that is the Worth of the Loan times the Mortgage Rate of it. In your example for Loan A = 10K and for Loan B = 5.5K. Instead of averaging the % (which is almost never a good idea) you'll work with these values. (10 + 5.5)/(200 + 100) = 5.167% which is what you want. In a single beast mode this can be written as:
SUM(`Worth` * `Mortgage Rate`)/SUM(`Worth`)
Which will work on the aggregation level used for your data.
Re: Has anyone used Illustrator to create an SVG for custom filter charts successfully?
@Ashleigh Thank you, taking a look at that video! If I ever get this to work, I will post the results on here for everyone. :)
deona720
Re: How to add gross profit row into profit and loss pivot table
We created something similar for our P&L where we needed to show net income or ebitda… we did this by creating a beastmode field to calc the two based on subtracting or adding revenues vs expenses and created a filter to toggle the grand total between the two.
jguzman