Related to finding summary and then writing back that summary

Hello All,

I am new to alteryx, i have a business case, where i have a table in the following format.

State	Zip Code	tax collected(lakhs)
Rajasthan	302019	67
Rajasthan	302019	78
Rajasthan	302029	98
Rajasthan	302029	56
Rajasthan	302019	76
Maharashtra	411027	7
Maharashtra	411098	85
Maharashtra	411027	55
Maharashtra	411678	7

The user want the summary of tax just below the category . for e.g.

State	Zip Code	tax collected(lakhs)
Rajasthan	302019	67
Rajasthan	302019	78
Rajasthan	302029	98
Rajasthan	302029	56
Rajasthan	302019	76
Total	302029	154
	302019	221
Maharashtra	411027	7
Maharashtra	411098	85
Maharashtra	411027	55
Maharashtra	411678	7
Total	411098	85
	411027	62
	411678	7

can we do this . Please suggest

Accepted answers

markcurry

I think this workflow should do what you are looking for...

Totals Table.PNG

Totals Output.yxmd

All comments

markcurry

I think this workflow should do what you are looking for...

Totals Table.PNG

Totals Output.yxmd

nazuk

Thanks Mark. Can you also suggest me one thing where i am stuck. So basically i have a input file which is a excel file with a dynamic name as TSE082QT_DD_MM_YYYY_HHMM . This excel file has only one sheet and that also have dynamic name as sheet name. So i tried when only the file name is dynamic, we can use wildcards. But in my case the only solution we could get is through "dynamic input tool" . Since i am a beginner i am unable to understand how we have to create a formula. Can you help me on this as well?

nazuk

Hello @markcurry ,

I am still stuck at this level, as user added few more formulas for creating the output:

So now if input is

State	Zip Code	tax collected(lakhs)	ID
Rajasthan	302019	67	P1
Rajasthan	302019	78	P1
Rajasthan	302029	98	P2
Rajasthan	302029	56	P1
Rajasthan	302019	76	P2
Maharashtra	411027	7	P1
Maharashtra	411098	85	P2
Maharashtra	411027	55	P1
Maharashtra	411678	7	P1

The output should be:

Date	State	Zip Code	tax collected(lakhs)	Population(crores)	ID
1-Feb-20	Rajasthan	302019	67	14	P1
1-Feb-20	Rajasthan	302019	78	23	P1
1-Feb-20	Rajasthan	302029	98	21	P2
1-Feb-20	Rajasthan	302029	56	23	P1
1-Feb-20	Rajasthan	302019	76	12	P2
Total			375	93

P1			201	60
P2			174	33
1-Feb-20	Maharashtra	411027	7	78	P1
1-Feb-20	Maharashtra	411098	85	98	P2
1-Feb-20	Maharashtra	411027	55	56	P1
1-Feb-20	Maharashtra	411678	7	76	P1
Total			154	308

Below are the rules:

Group by State:

1. if group name start with R, then :

1.Just after all records write sum for population and tax.

2. leave a blank row after this

3. Group by id P1 and P2 and write sum for pulation and tax seprately for both the id.

4. Leave a blank row after this.

2. if group name start with R, then :

1.Just after all records write sum for population and tax.

2. leave a blank row after this

Can you please help me on this?

markcurry

I think the attached workflow will do it for you. So each section is given a sort number, so the normal detail lines are 0, Total Line = 1, Blank Line = 2, and the ID subtotals are 3,4....

Totals Output.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15881

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703