mhouston Contributor

You want to put your sum() around your case statement: sum(case when `Expense Type` = 'Expenditure' then `Account Balance` else 0 end) - sum(case when `Expense Type` = 'Reimbursement' then `Account Balance` else 0 end)

@MarkSnodgrass thank you!! This was really helpful (and much simpler than what I was thinking) - I was able to take this and run with this to get what I need. Writing up the full solution for anyone who runs into something similar: I anonymized a subset of the data and ended up finding some edge cases I hadn't considered -…

You are going to need Editor access to complete the certification. It's a 2 part exam - part 1 is a multiple choice timed exam (so having editor access here isn't necessary although it could be helpful to check things in the UI). Part 2 is a practical exam which consists of several activities that you have to complete in a…

@Cterp My apologies - I mixed up the connectors. The Domo Governance Third Party Connector is the one that has the "Cards and Datasets" report: KB article: https://domohelp.domo.com/hc/en-us/articles/360056318074-Domo-Governance-Datasets-Connector

@Cterp you can get this info from the Cards and Datasets Report from Domostats. It has the card id, title, page id, page name, and the dataset id and name powering the card. I use this under a table card and just filter by the page name to quickly see what datasets are powering the cards on that page.

@FreshSqueezed you would have to use different logic in an ETL because the formula only executes on that row. I think you'd have to join the data to itself on lag date = exec date so that you'd have a second column for lag inv amount... beast mode is definitely going to give you more flexibility. @GrantSmith has a good…

Thanks for adding this! It looks like your chart has the date range filtered to this year - so the beast mode I gave you is not going to work unless you remove that date filter. It looks like the current configuration has no beast modes - so your ETL is calculating the previous year actual? Or it is coming in from the ERP…

@FreshSqueezed I plugged the first 10 rows of your sample data into that same demo card config and those last year formulas do work: can you share a screenshot of your card config?

There must be something else in your card configuration that's filtering out the data. I just loaded that dummy dataset into my own instance and created the beast modes and it's showing data:

@FreshSqueezed re-reading, that makes sense. Since you are doing your lag date in ETL, I am assuming your data looks like this: So what you want is for last year actuals to return the 150 from the second row, but your calculation is going to return the 100 from row one because lag date = 1/27/2021. I would either write…

Yes, I would expect that to work.

as far as your beast mode with lag date not returning - if I'm understanding your explanation correctly, the max possible value in your lag date field would be today's date minus 12 months (assuming your exec date cannot be greater than today's date) - in which case you will never have year(lag date) = year(current date),…

@FreshSqueezed I'm making the assumption that your date filer is on 'Exec Date' field. If so, you can't set the date range filter on the card to this year and still get previous year actuals. You will need to remove the data filter (or broaden it to include this year and last year). I think Mark's original solution is on…

@FreshSqueezed looking at your data check card it looks like you have it filtered to this year by month. Is your original card filtered the same way? If so - I think that's your problem because you're filtering your dataset to only rows in that time frame i.e. you have no rows in your dataset for previous year.

@FreshSqueezed in your ETL are you appending the old ERP dataset to the new ERP dataset? Are your column names from your old ERP Dataset the same as your new ERP Dataset? I did something similar when we migrated our BD/marketing system, and in my ETL used a select columns on our old dataset to rename the columns to align…

@JunkDoom I asked my CSM this same question and he confirmed it doesn't count against our row limit since they aren't materialized. You will still see them counted as part of your row count in the domo warehouse though.

@kboudrie and @Culper_Jr also the MajorDomo certification is inclusive of both the Domo Profession and the Data Specialist trainings/certifications (so you cover all the material in those and take those exams too, meaning you'll take a total of 6 exams).

I second @MarkSnodgrass answer, and would also like to add that the 12 hours of work estimate also includes the actual training materials you review as part of the certification prior to taking the exam. Within the certification, the knowledge test (multiple choice) is a timed 2 hours, and the practical exam is an…

@loganherzog could you create a new column (i.e. StoreNo_PDP) using the formula tile for use in the PDP policy that meets either of @GrantSmith suggestions? Then you would not risk altering downstream ETL's.

@User2021 are you saying that if A.a = B.a and A.b = B.b then check if either A.c = B.c or A.d = B.d? If so, I think this is the case statement logic you want: CASE WHEN (A.a = B.a and A.b = B.b ) THEN (CASE WHEN A.c = B.c THEN 1 WHEN A.d = B.d THEN 1 ELSE 0 END) ELSE 0 END

@daniel_cruz_2016 are your users viewing it at the dashboard level? Do you have the "Select all" at the top of the card when viewing in a dashboard? I am able to do what you describe when using a filter card on a dashboard - the top left has a select all (#) option, and after selecting that you can unselect one or two.…

@AnaKS I had this same issue (also doing the domo certification exam) and have opened a ticket with Domo Support for it but have not yet received a resolution. I'd echo Grant's suggestion and log a ticket with Domo Support.

done!

You can set a different landing page for your users if you're trying to have them log in to not a blank page? https://domohelp.domo.com/hc/en-us/articles/360043439093-Setting-the-Landing-Page

@MarkSnodgrass thank you! I didn't know this card type existed but I think it's exactly what I was looking for!

@MarkSnodgrass how do you add the color to your dynamic text? Are you just using the standard notebook option to add color manually? Or are you using a different card type when you say textbox?

@Canioagain you can get the opportunity history and field history using the domo salesforce connector and using the "Browse objects and field" option and selecting OpportunityHistory and OpportunityFieldHistory. I did a basic writeup when I built this that outlined the main logical steps of processing - let me know if you…

@Canioagain this was something I tackled about a year ago in our domo instance. As far as I could find, there was no easy way to query it out of salesforce - it doesn't seem that there is an opportunity history object you can query. I created a new salesforce dataset (salesforce_daily_append) that snapshots the salesforce…

You can create a beast mode and use that in your card instead of the field containing the number. CASE WHEN number = 1 THEN 'Insufficient' when number = 2 THEN 'Regular' when number = 3 THEN 'Sufficient' when number = 4 then 'Outstanding' end You'd replace "number" with whatever your field name is.

@MichelleH thanks!! that's really helpful to know about