topic Set analysis to manipulate a dimension in QlikView

Set analysis to manipulate a dimension

burgersurfer — Tue, 08 Jun 2010 20:58:27 GMT

I have a set of products, and for each of them, a set of sizes. These products are held and sold across various stores, and I want to see what the sales, and current stock holding is for a subset of the products

So I have as dimensions, the product code and product size. When selecting a specific date or product, I correctly get all the stock and sales info summed over all the stores. But when I select a store to get their detail, I am left with only Stock holding and sales lines for items that was actually SOLD.

I want to see what the current stock holding is, irrespective of whether there were sales of the product or not. The stock holding expression already employs set analysis to calculate all units irrespective of date etc selection, but still, when drill into the detail of a store, I only get lines for the products that have sales against them.

Do I use a calculated dimension to fix this, and what is the syntax then?

oh, tried the 'Show all values' tick box, but this then gives me ALL sizes, even if the size is not associate with the product....

Set analysis to manipulate a dimension

burgersurfer — Tue, 08 Jun 2010 22:57:04 GMT

Anyone with some ideas?

Set analysis to manipulate a dimension

Oleg_Troyansky — Tue, 08 Jun 2010 23:16:38 GMT

A few ideas:

I think you can try a Set Analysis within your Inventory expression, to ignore the Store selection - something like {<Store=>}.
People might have more ideas if you posted a reduced sample of your problem
Generally speaking, problems of this kind need to be solved in the Data Model, but the actual solution might be a bit too complex to describe here...

cheers,

Set analysis to manipulate a dimension

burgersurfer — Tue, 08 Jun 2010 23:37:43 GMT

Thanks Oleg. I tried removing the store selection as shown, but then I am left with stock info summed across all stores. My expression now exludes nearly everything in the model:

=Sum({<CalendarDate = , FinYear = , CalendarWeekDay = , CalendarDay = , CalendarMonth = , CalendarQuarter = , CalendarWeek = , FinMonth = , FinQtr = , FinTxtMonth = , TD_QTY = , TD_SUB_TYPE2 = , SIZE = ,
SZ = , SKU_TRANSACTIONS = >} PL_QTYONHAND)

The data model looks like this:

Here is a sample of the detail that I get when selecting a product only, but not any store (and what I want to get per store)

BUT as soon as I select a store, the result is reduced to this.

There is stock holding on the other sizes for this location.

Set analysis to manipulate a dimension

Oleg_Troyansky — Wed, 09 Jun 2010 00:12:35 GMT

OK, I see... You data model needs a lot of help, - much more than can be offered in the format of this forum. I'd say that you need some help from an experienced QlikView consultant.

The specific problem is caused by the fact that you have a number of instances for the field "Store".The Store associated with On Hand Balances is different from the main "Store" which is associated with Transactions.

The link between the table called "Stores" and the table holding On Hand values, is going through the "Transactions" (I suppose Sales...). So, what's happening is - when you select Store (from the Store Table), the association line, running through the Transaction table, is forcing the association with only those Items that had transactions. To avoid that, you need to connect On Hand Values with Stores and all corresponding Dimensions directly, avoiding the link through Transactions.

In your database, you need to get as close as possible to the Star Schema. You can do it if you concatenate Transactions and Inventory Balances and link the rest of the data accordingly.

cheers,

Set analysis to manipulate a dimension

burgersurfer — Wed, 09 Jun 2010 05:42:18 GMT

Thanks Oleg, I will work on the concatenation option.