Hi, I'd like to summarize my data by 5 different fields and add an element to each field "All" that contains the summarized data, which I have mostly done (sample workflow attached). The issue I'm having is that the "All"s don't intersect. For example, SourceMonthStoreProductSegmentDevicePayment TypeValue 1Value 2sys2016-02-01MerchandiseBasicStandardMobileCredit11sys2016-02-01 GiftBasicStandardTabletCash11sys2016-02-01AllBasicEliteTabletCash11sys2016-02-01AllBasicStandardMobileCash22sys2016-02-01GiftAllEliteTabletCash11sys2016-02-01GiftAllEliteTabletCredit11 This works fine if I only want to see "All" Store broken down by the various Products, Segments, etc. But if I want to see "All" Store and "All" Product by Segment, Device and so on, it doesn't work since there is only one "All" per row (the "All"s don't intersect at the row level). I'm not quite sure how to explain this more clearly, so my apologies for any confusion. Thanks for the help. Summarize Sample.yxmd

Summarize multiple fields with intersection

Hi,

I'd like to summarize my data by 5 different fields and add an element to each field "All" that contains the summarized data, which I have mostly done (sample workflow attached). The issue I'm having is that the "All"s don't intersect. For example,

Source	Month	Store	Product	Segment	Device	Payment Type	Value 1	Value 2
sys	2016-02-01	Merchandise	Basic	Standard	Mobile	Credit	1	1
sys	2016-02-01	Gift	Basic	Standard	Tablet	Cash	1	1
sys	2016-02-01	All	Basic	Elite	Tablet	Cash	1	1
sys	2016-02-01	All	Basic	Standard	Mobile	Cash	2	2
sys	2016-02-01	Gift	All	Elite	Tablet	Cash	1	1
sys	2016-02-01	Gift	All	Elite	Tablet	Credit	1	1

This works fine if I only want to see "All" Store broken down by the various Products, Segments, etc. But if I want to see "All" Store and "All" Product by Segment, Device and so on, it doesn't work since there is only one "All" per row (the "All"s don't intersect at the row level). I'm not quite sure how to explain this more clearly, so my apologies for any confusion. Thanks for the help.

Summarize Sample.yxmd

Transformation

Preparation

Accepted answers

jdunkerley79

I think what you want is summarising at 'all levels'

I created a workflow which uses a row generator to create 32 sets and then summarise the expanded set.

Could be sluggish on a large set though.

Summarize All Levels.yxmd

All comments

jdunkerley79

I think what you want is summarising at 'all levels'

I created a workflow which uses a row generator to create 32 sets and then summarise the expanded set.

Could be sluggish on a large set though.

Summarize All Levels.yxmd

jjc42

@jdunkerley79,

Thanks for your response. That seems to work as needed, and is much simpler than what I was originally trying to do.

I don't quite understand all of the steps you've taken, particularly how you determined 32 sets and the use of "BinaryAnd". Would you mind explaining briefly?

Thanks.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703