Calculations using Contexts.. 1

[binboy77]

I want to calculate sum on a column that has been calculated using the Previous() function. There is a measure field called <Cell Count> and i have created another column in report which has formula Previous(<cell count>). There is a section break in the report based on another dimension. Now, the Previous(<cell count>) works fine with the section breaks, but when i do sum(previous(<cell count>)), it also adds the last value of <cell count> from the previous section break. How to avoid this problem?

An example of the problem:


Section break----level one

Dimension Previous(cell count) Cell count
-----------------------------------------------
xy 2
xz 2 3
yr 3 5
----------------------------------------------
sum: 5 10


Section break----level two

Dimension Previous(cell count) Cell count
-----------------------------------------------
er 1
we 1 2
ee 2 3
-----------------------------------------------
sum: 8 <-Problem here 6


Note that the sum for &quot;Previous(cell count)&quot; for &quot;Section break----level&quot; two should be 2+1 = 3 but BO takes it as 2+1+5 = 8 where the 5 is the &quot;cell count&quot; of the previous column from the previous section.

How should i avoid taking the 5 (the value from the previous section) in this addition?
I tried defining contexts but unable to get the required results.

Any ideas on this would be of great help to me.

Thanks in advance

Bin

 

[skrandel]

Use if logic to avoid this problem. You can check to see if a dimension has the same value as the previous version before applying you formula.

But, from looking at your question, I think you'd be better off with the RunningSum or RunningCount functions. You can then set the &quot;reset&quot; context.

Steve Krandel
Knightsbridge Solutions
formerly BASE Consulting Group

http://www.knightsbridge.com

 

[binboy77]

Thanks Steve,

I tried with the RunningSum and reset context but still getting the same problem.
When i do a Previous(<Cell Count>), it gives me correct results and it takes all the dimesnsions and applies the section break correctly, but when i do a Sum(Previous(<Cell Count>)), it actually takes the Previous(<Cell Count>) of the first cell of each section and this makes it to add the <Cell Count> of the last Cell of the previous section also.

I know this is a bit difficult to explain, but i hope i am clear. Please do let me know if i need to explain this problem more clearly..

Thanks
Bin

 

[skrandel]

You are very clear and I can easily reproduce this. I'm playing with it and will let you know if I can come up with something.

It has to with the context that is being applied in the footer.

Steve Krandel
Knightsbridge Solutions
formerly BASE Consulting Group

http://www.knightsbridge.com

 

[Sridharan]

Bin,

I was able to reproduce the same thing using eFashion and able to get the correct value as per your need. Check it if that helps.

Take Year , Quarter and Quantity Sold. Set Year as Master and create a new variable(Previous Value) as

=If(<Year>=Previous(<Year>)) Then Previous(<Quantity sold>) Else 0

Now have that variable in the Table. Now do a sum it works great. Drop a note if you need more details/assistance.

Sri

 

[skrandel]

Sri,
It seems to work OK when you only have 1 Master item. But, when you have 2, it seems to have a problem.

Steve Krandel
Knightsbridge Solutions
formerly BASE Consulting Group

http://www.knightsbridge.com

 

[Sridharan]

Yes Steve. It does have a problem. But changing the IF Condition again seems to do the trick. With Year, Quarter, Month, Quantity Sold and Year and Quarter as Masters the formula changes to

=If(<Year>=Previous(<Year>) And <Quarter>=Previous(<Quarter>)) Then Previous(<Quantity sold>) Else 0

This gives the correct answer. But you know once after doing that I took out the IF ELSE Condition and even then BO gave correct results. I closed the document and created a new one but again wrong results. Bit strange.

Sri

 

[binboy77]

Hi Sri, Thanks a lot for that tip, thats some light at the end of the tunnel. I was also struggling with this problem for the past few days. Thanks.
But just wanted to know why BO behaves like this? Quite strage though. Why does even changing the contexts with the SUM(PREVIOUS(<Dimension>)) dosen't give me the right results as claimed by BO?

I think, this should solve my problem and thanks once again. Also thanks to Steve and all others for putting in the effort to help me with this problem.

Bin

 

[Sridharan]

Bin,

Glad to be of assistance .

Well Sum(Previous(<Dimension>)) seems to take values ignoring the Sectioning. But as I mentioned in the Post once I got that to work without IF ELSE stuff. Yeah bit strange. Sometimes BO works that way. I have experienced it in the past in other problems as well.

Atlast a Vote in BO Forum ;-).

Sri