Summing multiple entries

[alexisjensen]

Hi,

I am a beginner in Alteryx and need help with a particular summing issue.  I have data where there are multiple volume entries per day per region.  I want to convert the data to one summed entry per region per day.  eg:

From:

North    01/10/2015   100

North    01/10/2015   150

North    01/10/2015   125

To:

North   01/10/2015   375

I can get the result required if I use cross tab with date in rows and Region in columns.  However I want to have it as above with Region / Date / value as a single row.  This will help for later analytics reporting.

I appreciate your help.

Regards,

Alexis 

[s_pichaipillai · Accepted solution]

Hi Alexis 

Its Simple :)

Connect your data with Summarize tool  (its under Transform)

Group the data by Region and date

Sum the Value

Run it . you should see the data now

Note: Make sure your Vlaue for Summing is in Numeric Data type :)

Let me know if this works , else i will give a workflow 

[alexisjensen]

Hi,

Thanks for that.  I tried using summarize but didn't understand the group by rules correctly.  I have made it work with Group by Region and date and have 4 new columns of data as below:

Region	Date	Data 1	Data 2	Data 3	Data 4
East	01/01/2016	100	200	202	125
East	02/01/2016	150	200	105	150
North	01/01/2016	150	150	100	200

If I want to add a new field and transpose as below do I use the transpose tool?

Region	Date	Data Type	Value
East	01/01/2016	Data 1	100
East	01/01/2016	Data 2	200
East	01/01/2016	Data 3	202
East	01/01/2016	Data 4	125
North	01/01/2016	Data 1	150
North	01/01/2016	Data 2	150
North	01/01/2016	Data 3	100
North	01/01/2016	Data 4	200

Thanks Again.

[s_pichaipillai]

Alexis 

Here you go

Connect your data to Transpose Tool 

In the Transpose Tool Configuration Select the Key Fileds which you dont want to Transpose (your case Region and Date)

Under Data Fields, select the Value field that you want to Transpose(your case column name start with data1....Data 2 etc)

thats it 

Tip: by Default the Transpose names the Transposed Fields as Name and Value, you can use the select tool to rename your Fileds whatever you want

Hope this helps and refer the attached screen