Datasets join issue

I'm trying to create a card that can be used to present to budget owners how much they have currently spent for the month compared to the budget they've been given. In order to do so, I have to create a dataflow that involves joining 2 datasets; One is Netsuite, which stores all the journal entry transactions, and the other is Adaptive, our forecasting software that stores the monthly budget for each dept.

The error I'm running into is when I'm joining the depts. I'm joining by the department name from both Netsuite, and Adaptive. It runs, but when creating a card, (I'm using table), the sum of amounts for both the budget for the month, and what has been spent are in the billions for all GL accounts. This leads me to believe that when joining the two datasets, bunch of duplicate rows are being created. I've tried all 4 types of joins and it's the same issue each time. When keeping these datasets separate, the cards I create are fine (Budget info shows correct values, and same with what's been spent for the month)

Answers

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷

    It sounds like your Netsuite data is transactional level, and your Adaptive data is monthly. You need to aggregate your Netsuite data to monthly prior to joining with your Adaptive data. You can do this with a group by tile prior to your joining tile. You will need to join on department and month/year, not just department. Whatever your date field is in your Adaptive data, you will need to create the same in your Netsuite dataflow. There are several ways to do this with a formula tile, but before suggesting too much, let me know if this makes sense and is in line with how your data is structured.

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • GrantSmith
    GrantSmith Indiana 🥷
    edited March 31

    Make sure you’re filtering your data after your join so the month of the journal entry transaction is the same as the month of the budget forecast.

    You could do something like this in a conditional filter:

    LAST_DAY(`Adaptive Date`) = LAST_DAY(`Netsuite Date`)
    

    LAST_DAY is a function that returns the last date in the month (2022-03-31 for 2022-03-15 for example)

    It's a shorter way of doing:

    YEAR(`Adaptive Date`) = YEAR (`Netsuite Date`) AND MONTH(`Adaptive Date`) = MONTH(`Netsuite Date`)
    


    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**
  • It sounds like you have different data granularity in your datasets and that is causing the the duplicate rows. It sounds like your budget data granularity is month/department and your actuals are date/dept/account(?). If you're just joining on department, you're probably getting a cartesian join of every month X every transaction.

    You could fix the join by either aggregating your datasets to the same level (i.e. group by on your actuals to get a monthly spend and then join on dept and month for the two datasets).

    I would actually do an append instead of a join - this will provide more flexibility, and you can use beast modes in your cards to report as needed but it gives you the flexibility to still be able to drill down to transactional level information if you need to. @jaeW_at_Onyx has a great youtube video that outlines the general methodology https://www.youtube.com/watch?v=PVbOeLSae9o