I have data (input) on which I need to perform some calculations to get the result that I want (output). Input: SalesStoreProductCategoryQuantity500A1Inhouse40205B2External25360C1Inhouse35400D2Inhouse30155E3External10268F4Inhouse20306G1External25478H3Inhouse28121I4Inhouse12 Output: Product 1Product 2Product 3Product 4Total Sales Total Quantity Average Quanity Inhouse Inhouse as %ge of total Please note that the above data is just a sample. Actual data is quite large. Can you help me get the above output from the given input? Thanks in advance!

Creating a table with row and column headers

I have data (input) on which I need to perform some calculations to get the result that I want (output).

Input:

Sales	Store	Product	Category	Quantity
500	A	1	Inhouse	40
205	B	2	External	25
360	C	1	Inhouse	35
400	D	2	Inhouse	30
155	E	3	External	10
268	F	4	Inhouse	20
306	G	1	External	25
478	H	3	Inhouse	28
121	I	4	Inhouse	12

Output:

	Product 1	Product 2	Product 3	Product 4
Total Sales
Total Quantity
Average Quanity
Inhouse
Inhouse as %ge of total

Please note that the above data is just a sample. Actual data is quite large.

Can you help me get the above output from the given input?

Thanks in advance!

Reporting

Output

Transformation

Accepted answers

FinnCharlton

Hi @Dheeru28 , here is one way to do this:

First, we use summarise and filter tools to generate the aggregate values we need. We then use a formula tool to calculate the 'Inhouse Sales as % of total'. The last two tools transform the data into the structure you need. Hope this helps!

Summarising Sales and Quantity.yxmd

IR_2021

Hey,

This is a way to do this. I hope it will work for you.

row and column headers.yxmd

All comments

FinnCharlton

Hi @Dheeru28 , here is one way to do this:

Summarising Sales and Quantity.yxmd

IR_2021

Hey,

This is a way to do this. I hope it will work for you.

row and column headers.yxmd

Dheeru28

@FinnCharlton and @IR_2021 Thank you very much. It works.

There is one more thing: If I want to create a new field as a row header (in row 6) by multiplying total sales(1st row) with total quantity(2nd row) for each of the products. How do I go about doing that?

In actual data, there are multiple such calculations to be done. So is there a way to do it easily?

Also, is it possible to create pivot tables in Alteryx as we can do in excel?

FinnCharlton

@Dheeru28 , you'll want to think about how your data is structured. It is very easy to multiply different fields (columns) in Alteryx, but not easy to multiply values from different rows. I would recommend you structure your data so that each metric (Sales, Quantity, Inhouse Sales) is a COLUMN, and each product is a ROW. This is the data structure at this stage of my above answer:

You can then easily use a formula tool to do your calculations. If you really need your data in your initial structure, you can change it after you do all your calculations.

As for Pivot Tables, you can accomplish the same using combinations of the Transpose, CrossTab and Filter tools. It takes a bit of practice.

Dheeru28

Thanks a lot, @FinnCharlton !!

I actually need the output in that initial structure.

I will try doing that way and see if I am able to get what I want.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703