7 Replies Latest reply: Aug 30, 2013 9:06 AM by Erik Furlanis RSS

    Calculated dimension: SUM is yielding error (with or without IF statement)

      Dear all

       

      This is the nth topic about calculated dimensions and summing and if and aggregation...

      I am trying to create a calculated dimension which check whether the sum of qty is higher than the value which is on field A.

       

      Data in the pivot chart is shown per SKU levels.

      Qty data comes from lines, therefore there are several lines per SKU (in the backgroung)

      Meanwhile data in field A is one single value per SKU

       

      SOURCE DATA

      Table 1

      SKUField A
      AA5
      AB2
      BB6
      CC7

       

      Table 2

      SKUqty
      AA3
      AA2
      AA4
      AB2
      CC1
      CC4

       

      MY EXPECTATION

      calculated dimension: if ( sum(qty)>=Field A, 1, 0)

       

      SKUField Asum(qty)Calculated dimension
      AA591
      AB221
      BB600
      CC750

       

      So I hope this example is pretty self explanatory.

       

      I am having trouble with the calculated dimension, as apparently it cannot handle the Sum of different lines, let alone the IF statement.

      If I try sum(qty) or aggr(sum(qty)) or other weird combinations, it always yields

       

      //Error in calculated dimension.

       

      Can please someone help me to crack this? I tried applying solution of several other threads, but indeed I'm still at square one

      thank you so much!