Datasets join issue

j_jungy
j_jungy ⚪️

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

  • GrantSmith
    GrantSmith Indiana 🥷

    What logic are you using to join your datasets together? If you're just joining on the department then you're joining every departments spend across all the days in your dataset. You need to make sure to join on the same dates.

    A better methodology would be to stack/append your data such that you have a date, amount and metric ("Spend" or "Budget")

    Then you can use a beast mode to calculate spend or budget independently in your graphs or use the metric field as your series

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**