How to locate => value in different column of Pivot table.

[#1]

I've tried a great deal of methods to make this work and I'm simply running out of ideas at this point.  I've searched this site until my fingers are sore and still no luck.

I have a pivot table that has two different expressions in it. I'm trying to write a third expression that will take one value from a Expression A plus the current total of Expression B and then count the columns between it and the first matching or larger value in Expression B that will occur some weeks later.  Both expressions are rolling totals across a weekly Dimension using Rangesum and Before with ColumnNo.  The gaps in weeks will be more or less random, so I have to be able to more or less search for the value or do a count.  The biggest issue I'm running into is that it's very difficult to compare the current amount of Expression A plus Expression B and then find the nearest analog to it in X columns to the right.

I've tried to nest 53 weeks of If(After(Column(2), but it won't all fit in the expression box.  If someone can give me a good example of how to count the weeks between (Expression A + Expression B) to the later/larger version of Expression B, I would really appreciate it.  Given the way this data is from very disparate sources with gaps in the weeks etc, the interval match command doesn't seem like a viable option.

Thanks in advance.

Scott

[fosuzuki]

Can u post some sample data? Something like a sample qvw with what you've achieved and what is your requirement?

[#3]

  Here is the code for the expression.

(=(IF(Column(2)<='0','0',53-ColumnNo()-

(IF((Column(3)-Column(2))>=(After(Column(3),1,1)),(51-ColumnNo()+1),

(IF((Column(3)-Column(2))>=(After(Column(3),2,1)),(50-ColumnNo()+1),

(IF((Column(3)-Column(2))>=(After(Column(3),3,1)),(49-ColumnNo()+1),

(IF((Column(3)-Column(2))>=(After(Column(3),4,1)),(48-ColumnNo()+1),

(IF((Column(3)-Column(2))>=(After(Column(3),5,1)),(47-ColumnNo()+1),

****skips several lines to finally arrive at*****

(IF((Column(3)-Column(2))>=(After(Column(3),46,1)),(6-ColumnNo()+1),0))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

-(IF((Column(3)-Column(2))>=(After(Column(3),46,1)),'0',

(IF((Column(3)-Column(2))>=(After(Column(3),47,1)),(5-ColumnNo()+1),

(IF((Column(3)-Column(2))>=(After(Column(3),48,1)),(4-ColumnNo()+1),

(IF((Column(3)-Column(2))>=(After(Column(3),49,1)),(3-ColumnNo()+1),

(IF((Column(3)-Column(2))>=(After(Column(3),50,1)),(2-ColumnNo()+1),

(IF((Column(3)-Column(2))>=(After(Column(3),51,1)),(1-ColumnNo()+1),0))))))))))))

I'm taking 52 weeks and checking every column to the right of it in the 52 weeks across pivot table with a nested if statement.  I found a way around the nesting limit as you can see at the bottom.  I'm just looking for a way to achieve this same thing without creating such a drain on system resources.

Column 3 is [Compound Demand]

Column 2 is [RNP]

Column 1 contains the above formula.

I can't paste data unfortunately.  That would violate company policy.

Thanks for the help.