Stores with no sales data no included in a report

[Amit_B]

Hey,

I want to show the number of  items sold for a certain category, by store.
The measure is simple - Sum(Qty).
My report should include all the stores.

The problem is some stores didn't sell any items from the relevant category,
so I don't see them at all in the report (table or pivot).

For example:
Store 1 - Sold 3 items on Jan and 2 items on Feb. Total of 5.
Store 2 - Sold 2 item on Jan and 1 item on Feb but this item was returned. Total of 2.
Store 3 - Didn't sell any items in Jan, and sold 1 item in Feb.
So in total:
Store 1 - Jan 3, Feb 2, Total 5.
Store 2 - Jan 2, Feb 0, Total 2.
Store 3 - Jan Null(?), Feb 1, Total 1.
We will see the problem if we select only for Jan - Store 3 will not appear in the report.

Enabling the 'show zero values' option doesn't work, using functions like Alt() or conditions in the measure (like If(Len<1, 0,..)) doesn't help either.

I'd appreciate your help.

[marcus_sommer]

A simple solution for such scenarios is to populate the missing data, maybe in way like:

t1: load Store from X; join(t1) load Item from Y; join(t1) Date from Z;

t2: load *, Store & '|' & Item & '|' & Date as Key resident t1;

concatenate(MyFacts)
load *, 0 as Value, 'populated' as Source
resident t2 where not exists(Key);