Summarize multiple fields with intersection

[jjc42]

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.

[jdunkerley79 · Accepted solution]

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.

[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.

[jdunkerley79]

I used a binary mask. You have five fields so I needed 2^5 ==> 32 numbers

Then looking at the binary representation of each number I create a mask:

If the first bit is 1 (i.e. BinaryAnd(x, 1) == 1) then set Field1 to 'All'

If the second bit is 1 (i.e. BinaryAnd(x, 2) == 2) then set Field2 to 'All'

...

This creates 32 masked sets of data (well 31 plus one with no masking).

Hope that makes some sense 

[jjc42]

I knew I was going to feel dumb after asking about the 32 sets. 

Thanks for the explanation.

[jdunkerley79]

Not at all, a sneaky trick to create a set of masks