Set Analysis dynamically referencing to dimension value in pivot-table

Mon, 04 May 2009 15:13:42 GMT

Hi.

I have a question regarding a set-analysis: If I calculate a formula using set-analysis with a certain selection, is it possible to additionally select the dimension values (dynamically)?

It that's possible within the set-expression, how would the syntax be?

As an example I got the following table:

ProductType	Customer (cyclic group)	year	sold per year
shoes	test1	2007	350
		2008	350
		2009	350
	test3	2008	350
	test8	2007	350
	test2	2008	350
total			0

Sold per year contains the following formula:

if(_Category='footwear' and Index('31,32,33,34',Right(CatID,2))>0,Count( distinct total {_Category = {"shoes","shoeslaces"},ProductType = {*}) sold_ID),sum(sold)))

What I need to achieve is that if certain IDs of our "footwear"-category apply, it should count the distinct IDs of another category, since I don't have a summable amount of sold units for these. Sadly, this formula doesn't take into consideration the dimensions in my pivot-table - leading to a amount of 350 wich is the number of sold units in total for ALL customers and years.

Strangely, in the total column nothing is being calculated?

Is there a way to dynamically tell the set-analysis formula to calculate for each line of the given table and select the corresponding values?

(e.g. ProductType = "shoes",Customer = "test1", year = "2007").

Set Analysis dynamically referencing to dimension value in pivot-table

Thu, 07 May 2009 00:06:39 GMT

It would be easy to assist if you upload a QVW with some sample data.

Set Analysis dynamically referencing to dimension value in pivot-table

Thu, 07 May 2009 15:08:01 GMT

Okay, i've found a solution to this. Adding in the dimension/group names after the total modifier seems to do the trick.

if(_Category='footwear' and Index('31,32,33,34',Right(CatID,2))>0,Count( distinct total <CustomerGrp,Year> {_Category = {"shoes","shoeslaces"},ProductType = {*}) sold_ID),sum(sold)))

topic Set Analysis dynamically referencing to dimension value in pivot-table in QlikView

Set Analysis dynamically referencing to dimension value in pivot-table

Set Analysis dynamically referencing to dimension value in pivot-table

Set Analysis dynamically referencing to dimension value in pivot-table