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

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