Summarizing data dynamically

Alteryxexpert · 2024-04-17T10:57:33+00:00

I have Data like below, ItemFilter Date31-Dec-2331-Jan-2429-Feb-24A31-Jan-244,545,4564,545,4564,545,456A31-Jan-24545,654545,654545,654B31-Jan-24###########################B31-Jan-24752,436752,436752,436C31-Jan-24689,535689,535689,535C31-Jan-2484,274,82747,382,78348,912,384 and I need the output as below, I need to Filter…

Alteryxexpert

I have Data like below,

Item	Filter Date	31-Dec-23	31-Jan-24	29-Feb-24
A	31-Jan-24	4,545,456	4,545,456	4,545,456
A	31-Jan-24	545,654	545,654	545,654
B	31-Jan-24	#########	#########	#########
B	31-Jan-24	752,436	752,436	752,436
C	31-Jan-24	689,535	689,535	689,535
C	31-Jan-24	84,274,827	47,382,783	48,912,384

and I need the output as below, I need to Filter out the columns based on the Filter Date, If the filter date is 31-Jan-2024 then the columns should start from 31-Jan-2024 to 31-Dec-2024, and the filtering is dynamic. Is there a way to achieve this?

Item	Filter Date	Sum of 31-Jan-2024	Sum of 29-Feb-2024
A	31-Jan-24	5,091,110	5,091,110
B	31-Jan-24	466,217,901	466,217,901
C	31-Jan-24	48,072,318	48,072,318

Workflow

Developer

Gallery

Developer Tools

Output

Custom Tools

Parse

Connectors

Common Use Cases

Best Practices

Accepted answers

All comments

aatalai

@Alteryxexpert see if this helps, had to drop the row with #### as I couldn't see the number

Summarizing data dynamically.yxmd

Alteryxexpert

The Output Column name should be "Sum of 31-Jan-2024" , "29-Feb-2024" etc.

aatalai

@Alteryxexpert take a look at this one

Summarizing data dynamically v2.yxmd

csh8428

@Alteryxexpert try this one.

If this works for you please select it as as the accepted answer.

Summarizing data dynamically example.yxmd

Gaurav_Dhama_

Best approach to make summarization dynamic is by updating the xml of the Summarize tool. You can put the summarize tool inside a batch macro, where the control parameter will have single input and update the xml of the summarize tool. XML can be generated outside the macro and passed as input.

Since XML going inside the macro will be a string you can create it by transposing the headers and getting the relevant headers.

cjaneczko

Try this. This is completely dynamic and will filter out the columns based on the filter date.

Summarizing-data-dynamically.yxmd

SPetrie

Assuming that the Filter Date is the same for all Items, this is another possible solution you can use.

summarize.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703