Two formulas for the same expression

[Anonymous]

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

F1	F2	F3
Item1	SubItem1	1
Item1	SubItem2	2
Item2	SubItem1	3
Item2	SubItem2	4

2. Transaction Table

Dim1(F1)	Dim2(F2)	Expression: Formula (logic)	Expression: Example
Item1     --	SubItem1	Sum(A_SubItem1) + Sum(SubItem1)	4+1=5
	SubItem2	Sum(Above) + SubItem2	5+2=7
Item1     --	SubItem1	Sum(Above) + SubItem1	7+3=10
	SubItem2	Sum(Above) + SubItem2	10+4=14

3. Transaction Table - Pivoted

Dim1(F1)	SubItem 1	SubItem 2
Item1	5	7
Item2	10	14

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

[swuehl · Accepted solution]

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

[Anonymous]

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

Thank you very much Stefan for your help!