2 Replies Latest reply: May 7, 2009 11:08 AM by Klemens Wittig

# Set Analysis dynamically referencing to dimension value in pivot-table

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

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

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)))