Include NULL rows in Summarize result

Question

I am trying to summarize sales by Week number and Product and there are no sales for some of the products during some specific weeks. How can I get the summarize result to still list these week nos. and products with 0 against the sales columns.

For a quick example:

Week No.      Product       Qty

1                      A               100

1                      B               200

2                      B               250

3                      A               100

3                      B               250

I want the above summary to include Week 2, Product A with Qty 0.

Appreciate any help!

mceleavey · Accepted Answer

Hi @Bhuvana_Shiv ,

A simple way to do this is to create a unique list of weeks and product, then append them. This will in effect create a cartesian join, which creates every possible combination of the two fields.

Then you can join these combinations back to the data, those of the new combinations you have created that don't join, you can give a Qty of 0 and union back together:

This will dynamically create the week and product combinations as they arise.

This gives the following:

Workflow attached.

I hope this helps,

M.