2 Replies Latest reply: Jan 16, 2013 3:04 PM by Adrian Dogaru RSS

    Two formulas for the same expression

    Adrian Dogaru

      Hello everyone; I've come across a requirement that so far I was unable to resolve. I'm hoping that someone might be able to point me to the right direction on how to do this both logically and technically.

       

      1. Data Table

      F1F2F3

      Item1

      SubItem11
      Item1SubItem22
      Item2SubItem13
      Item2SubItem24

       

       

      2. Transaction Table

      Dim1(F1)Dim2(F2)Expression: Formula (logic)
      Expression: Example
      Item1     --SubItem1Sum(A_SubItem1) + Sum(SubItem1)4+1=5

      SubItem2Sum(Above) + SubItem25+2=7
      Item1     --SubItem1Sum(Above) + SubItem17+3=10

      SubItem2Sum(Above) + SubItem210+4=14

       

      3. Transaction Table - Pivoted

       

      Dim1(F1)SubItem 1
      SubItem 2
      Item157
      Item21014

       

       

       

      So... this is what I need to get done: have one expression with two different formulas. Formula 1 will apply only to the very first row/column, while the second formula will apply to the rest of the rows/columns

       

      What I've tried so far:

      1. Created another intermediary table to get the "Starting Point"

       

      StartPoint
      4

       

       

      2. Use an IF statement to calculate the Expression:

           IF(F3='1',Sum(StartPoint+F3),Above(Column(4))+F3)

       

      The above gets me the correct first row/value but null afterwards; I've tried many different combinations, but never the result I wanted.

       

      I have attached my example qvw; any assistance with this will be much appreciated.

       

      Thank you,

      Adrian