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

# Two formulas for the same expression

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,

• ###### Re: Two formulas for the same expression

I think that you don't need the StartPoint field and you don't need a condition to check which calculation to evaluate, try as expression:

=aggr(rangesum(above(TOTAL F3,0,rowno(TOTAL))),F1,F2) + sum({<F2={SubItem1}>} TOTAL F3)

Check also attached.

Regards,

Stefan

• ###### Re: Two formulas for the same expression

It works like a charm;I guess I should start paying attention to set analysis .

Thank you very much Stefan for your help!