Dimensions and Measures

Options
kim_barragan0126
edited May 2 in Magic ETL

Hi,

Is there a way to use a dimension field also as a measure? Similar to what you do in Excel while using a pivot table?

Best Answer

  • david_cunningham
    Answer ✓
    Options

    It sounds like it's possible you may have ended up with more rows in your joined output dataset.

    The way that SUM(1) works is that it counts the number of rows in the set, so if you're getting more rows than expected it's possible that is due to the join.

    Do you have an ID column? If so, you could do something like

    COUNT(DISTINCT id)

    This would avoid any double counting going on from additional rows due to a join.

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

Answers

  • david_cunningham
    Options

    @kim_barragan0126 can you provide a little more detail on what exactly you are trying to accomplish? I'd be happy to try and help you out. You can aggregate dimensions (see image)

    This shows a count of date by day

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • kim_barragan0126
    Options

    Thank you. I want to use a dimension to get a count, such as the below. I want to know how many of the action, not the name such as R-Development.

  • david_cunningham
    Options

    To do that you can just use a counter column or create a beast mode. For example

    SUM(1)
    

    You would use this in place of Action in the Value section of the Pivot table.

    You'd then want to include the Action dimension as a row in the your pivot table. You can either put it above Recruiter or below. Above will show all Actions and the Recruiters assigned those Actions. Below will show all Recruiters with their respective Actions.

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • kim_barragan0126
    Options

    Thank you! I tried it in a beast mode, and the numbers are not correct. The numbers should be in the hundreds, not thousands. I have this working in other tables, and had it working in this one until I joined another data set with numbers. Could that have caused the issue?

  • david_cunningham
    Answer ✓
    Options

    It sounds like it's possible you may have ended up with more rows in your joined output dataset.

    The way that SUM(1) works is that it counts the number of rows in the set, so if you're getting more rows than expected it's possible that is due to the join.

    Do you have an ID column? If so, you could do something like

    COUNT(DISTINCT id)

    This would avoid any double counting going on from additional rows due to a join.

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • kim_barragan0126
    Options

    Thank you for all the help.

  • kim_barragan0126
    Options

    Yes. This might be a better screenshot.