Getting a total disregarding selections

[Anonymous]

This problem has turned out to be much harder than it should be.

To simplify the question, let's just say I need both the number of stores a product is sold in as well as the total number of stores, regardless of whether selected products/brands were sold there - As in, the total should be the total number of physical stores. (Stores NOT sold is very important for the measures on this pivot table.)

The pivot table needs to be something like this - Brand A would be selected, and Brand A's products shown:

Product stores sold	Brand	Region 1	Region 2	Region 3
Product 1	Brand A	0/5	3/7	5/8
Product 2	Brand A	0/5	6/7	0/8
Product 3	Brand A	0/5	0/7	7/8

So, if I have Product as one dimension and Region as another, pivoted dimension, it would seem that the expression would be a simple sum(StoreCount) & '/' & sum(total<Region> StoreCount). However, that will result in Region 1 not showing up at all, since there were no sales of that brand's products in that region. That also results in the total being incorrect because any store where Brand A isn't sold at all isn't counted.

Now, if I change the denominator portion of the expression to sum({1} total<Region> StoreCount), my product-related selections are disregarded and the table expands to show all products, but shows no sales of anything except the ones I have selected.

So, I need a way for my expression to ignore product selections for the total count while not causing deselected products to appear in the pivot table.

Thanks!

[hic]

You need to create records for all combinations of Product and Region already in the script. I.e. create "dummy" records with StoreCount=0 for the missing combinations. Otherwise these combinations are logically excluded and will not be included in the calculations.

There is a section in Generating Missing Data In QlikView that describes this.

HIC

[Anonymous]

For clarification's sake: StoreCount is not in the fact table, it's in the Store dimension table. I only want to count each physical store once, no matter how many sales they've had.

Also, in this case, the problem is more complex than the question I asked: The second dimension may be region, store, state, etc. and date selections are likely as well. Thus, to make this work, I'd have to populate zeros in the fact table for every store, every product, and every week. That would generate billions of rows in this case and I don't think it's a viable option. I've already come up with a way to show 0's where there is no data that doesn't require the extra fact rows.

I may need to just have a data island with a copy of the Stores table and possibly pass some selections from the main stores table using triggers.

[Anonymous · Accepted solution]

Solution: I created a separate Stores table like so:

Qualify *;

Unqualify Region;

TotalStores:

NoConcatenate Load * Resident Stores;

Unqualify *;

Then, just changed my expression to use TotalStores.StoreCount.

FWIW, generating 0's for missing data would result in an extra 14 million rows/week, so I've added 0 & to the beginning of the expression so that I get 0's where I want them without backfilling data, taking advantage of QlikView's loosely typed nature to fool it a bit. Works great.