topic Re: Set Analysis Question in QlikView

Set Analysis Question

Fri, 27 Mar 2015 16:31:15 GMT

We have a Sales and Inventory QVW document in which we have a tab where you can select a year,month and week number from a list box. Also we have a Multi Box for selecting information related to a Product like - SKU number, Product Class etc. We have another Multi Box for information related to Store like - Store Number, Store Name etc.

There is a Pivot Chart which reflects the change in data according to the selections made in these list boxes and Multi Boxes. The Pivot Chart has the following columns - Date Range (Week), Sales for current year and Inventory for current year.

Example:

I want to be able to pick a Product SKU and a week number that will c

When selected a SKU - '500'

What I want to achieve:

When selected SKU - '500' and then pick a store number - '309'. The store 309 had no transactions but we still want to see the Total Inventory although the Sales would show 0.

What's not Working:

Because there were no transaction in that store, all the selections get dis-selected and the Inventory changes to 0 too. And we don't want this to change to 0. It should still show 100.

How can I achieve this? So if there were no transactions in that particular store, the sales should change to 0 but the Inventory should not. It should still show the Total Inventory irrespective of the Store selection.

NOTE: INVENTORY TY is a metric.

I think this can be achieved using Set Analysis but I'm not sure how to implement it. Thanks for your help!!

Re: Set Analysis Question

Fri, 27 Mar 2015 16:56:38 GMT

Hi Vidit

You are correct, this can be achieved with set analysis. The will want update your Inventory measure calculation to use something similar to the following expression, updating the Store ID to your field name as appropriate.

=SUM( {$<[STORE ID]=>} [INVENTORY TY])

The part highlighted in bold is the set analysis that you will want to add to your aggregation calculation so that store selections are disregarded for the measure.

Reply or update post if this helps or not.

Kind regards

Re: Set Analysis Question

Fri, 27 Mar 2015 20:05:25 GMT

Hi Steven,

Thanks for a quick reply! I tried to add the set analysis part you gave above to my metric calculation (which is also a set analysis function) but did not seem to work. Am I doing anything wrong?

So Store_Key is the primary key of my Store table.

Original Metric Calculation:

sum({$<inv_retail_year={$(=max(Total retail_year))}, inv_retail_week_num={$(=min(Total retail_week_num))}>} FIELD_INV_CURRENT) + sum({$<inv_retail_year={$(=max(Total retail_year))}, inv_retail_week_num={$(=min(Total retail_week_num))}>} WAREHOUSE_INV) + sum({$<inv_retail_year={$(=max(Total retail_year))}, inv_retail_week_num={$(=min(Total retail_week_num))}>} FIELD_INTRANSIT) + sum({$<inv_retail_year={$(=max(Total retail_year))}, inv_retail_week_num={$(=min(Total retail_week_num))}>} WH_INTRANSIT)

Added your set analysis: {$<[STORE_KEY]=>}

sum({$<[STORE_KEY]=>}{$<inv_retail_year={$(=max(Total retail_year))}, inv_retail_week_num={$(=min(Total retail_week_num))}>} FIELD_INV_CURRENT) + sum({$<[STORE_KEY]=>}{$<inv_retail_year={$(=max(Total retail_year))}, inv_retail_week_num={$(=min(Total retail_week_num))}>} WAREHOUSE_INV) + sum({$<[STORE_KEY]=>}{$<inv_retail_year={$(=max(Total retail_year))}, inv_retail_week_num={$(=min(Total retail_week_num))}>} FIELD_INTRANSIT) + sum({$<[STORE_KEY]=>}{$<inv_retail_year={$(=max(Total retail_year))}, inv_retail_week_num={$(=min(Total retail_week_num))}>} WH_INTRANSIT)

Thanks

Vidit

Re: Set Analysis Question

rubenmarin — Fri, 27 Mar 2015 20:52:52 GMT

Hi Vidit, syntax should be:

Sum({<Set analysis>} Field)

You're using this syntax, wich is wrong:

Sum({<Set analysis>}{<Set analysis>} Field)

So, it should be:

sum({$<[STORE_KEY]=, inv_retail_year={$(=max(Total retail_year))}, inv_retail_week_num={$(=min(Total retail_week_num))}>} FIELD_INV_CURRENT)

Re: Set Analysis Question

Fri, 27 Mar 2015 21:13:54 GMT

Thanks Ruben! But it did not work.

Re: Set Analysis Question

rubenmarin — Fri, 27 Mar 2015 21:30:06 GMT

Hi Vidit, you changed the rest of the expression? Mine was only an example in the first Sum, but all the other must be changed in the same way.

Also, this wouldn't solve your problem with de-selecting when you select a store without sales, it should need a more complex solution, generating missing data or finding a way that keeps relation between products and stores even if they have no sales of that particular product.

Check this link, maybe it helps you:

Generating Missing Data In QlikView

Re: Set Analysis Question

pokassov — Sat, 28 Mar 2015 05:48:19 GMT

Hi, Vidit!

Could you attach your model?

Re: Set Analysis Question

Mon, 30 Mar 2015 14:26:26 GMT

Thanks Ruben!

Re: Set Analysis Question

Mon, 30 Mar 2015 14:32:09 GMT

Hi Capren! I'm not sure how to attach a qvw to this post. But this is the calculation I was using:

Re: Set Analysis Question

Mon, 30 Mar 2015 15:19:57 GMT

Ruben, yes I did change the whole expression accordingly but it did not work either.