Best Of
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
Re: How to add gross profit row into profit and loss pivot table
For each row in a Pivot table, a value needs to exist, so without joining a new Gross Profit Account, having this to display would be hard to say the least, and even then you'd need to work with the fact that the measure calculation will need to be worked differently on that one than on the others.
You might be able to achieve a similar outcome, using a Megatable and transposing it (rows being the months), that way the columns (Beast Modes with case statements on the Account) to represent the Trading Income, Cost of Sales, etc will become rows, but if you want the breakdown (Sales, Purchases, Interest Income, etc) to also be displayed without drilling down you'd need to create a Beast Mode for each and whenever a new one shows or one does not have entries, they won't be automatically added/removed.
Would love to see if anyone has other ideas that could come in handy for this.
Re: way to split column name into first and last
Here you go:
First Name:
LEFT(`name`, (INSTR(`name`, ' ')-1))
Last Name:
RIGHT(`name`, LENGTH(`name`)-INSTR(`name`, ' '))
Hope that helps,
Valiant
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.
Re: How do I Compare Column A vs Column B in the same Value?
@bstephan With this setup, changing the global date filter to Month to Date would exclude all of last year's data. The best way to make the YoY variances to dynamically update with the global date filters is to restructure your data in a dataflow to add a separate dataset column for prior year sessions. Then you could replace the prior year calculations with the prior year dataset column in your cards.
Re: Create a Function to extraxt a specific secuency of numbers
Assuming you're wanting the numerical value out of the hidTxtGonSearch field you can utilize a regular expression in a formula tile in Magic ETL.
REGEXP_REPLACE(`field`, '^.*hidTxtGonSearch=(\d+).*$', '$1')
Re: Question on Domo SQL syntax
@Aag2023 For the product key, you can transform this into a date using the STR_TO_DATE() function:
WEEK(STR_TO_DATE(`PRODUCT_KEY`,'%Y%m%d'))
Since `ProductSale_Timestamp` is already in a datetime format, @GrantSmith's formula should already work for that.
Re: How to create a filter using Beastmode?
@Buck You can create a beast mode that's shared on the dataset and put that in the quick filters section of analyzer instead of the original dataset field name. That way you only need to change the name once.
Re: How to create a filter using Beastmode?
CASE WHEN FRANCHISEE NAME IN('Adam','Al','Leon','Brandon','Colemen') THEN 'Include' ELSE 'Exclude' END
Then use it as a filter and select 'Include'. Also select "Share on Dataset" on the quick filter.
