Calculation in WEBi

[abhimanyu_sharma]

Hi Experts,

The report is on the material and batch Combination. So, we created groups on MATERIAL and BATCH.

We need to calculate the Aging Month Column.  It should be like:

On the first cell it should always be zero. But for the second cell it should be the difference of 03/01/2015 and 01/01/2014

For the third cell it should be the difference of 03/01/2015 and 01/01/2014.

So it means in each cell we have to subtract the run date with the very first Posting date.

Please suggest how can we do this in WEBi 4.0?

Material	Batch	Posting Date	Rundate	Original Transactoin Qty	Aging Qty	Aging Moth
MAT A	BATCH A	01/01/2014	03/01/2015	100	0	0
MAT A	BATCH A	02/02/2014	03/01/2015	150	100	14
MAT A	BATCH A	03/03/2014	03/01/2015	`120	120	14
MAT A	BATCH A	04/04/2014	03/01/2015	110	110	14

Material	Batch	Posting Date	Rundate	Original Transactoin Qty	Aging Qty	Aging Moth
MAT B	BATCH B	01/01/2014	03/01/2015	100	0	0
MAT B	BATCH B	02/02/2014	03/01/2015	150	100	14
MAT B	BATCH B	03/03/2014	03/01/2015	`120	120	14
MAT B	BATCH B	04/04/2014	03/01/2015	110	110	14

[Former Member]

Hi,

Take a look at this link for the months between:

http://www.forumtopics.com/busobj/viewtopic.php?p=798949&sid=ad3a8e483e2e890722553347a601b541

For the first row to be 0, you need to add a variable using the rownumber() function. Then you can say something like "If rownumber = 1 then 0 else Mycalculation"

Hope this helps.

/Kris

[abhimanyu_sharma]

Hi Kris,

Could you please provide the calculation based on the example quoted by me.

I checked the link sent by you but i didn't get anything from it related to it.

[Former Member]

Hi,

Sure, here's my report - you can just remove the "_Rownumber" object from the table, and it will still work:

And here are the formulas for the objects:

_Rownumber

=RunningCount([Store name])

_Aging month

=If([_Rownumber]=1) Then 0 Else MonthsBetween([_Posting Date];[_Rundate])

Regards,

Kris

[Former Member]

Create a variable at the report level for days between and the date objects to get the number of days

[abhimanyu_sharma]

Hi Avinash,

That is Ok.

But my requirement is to :

On the first cell it should always be zero. But for the second cell it should be the difference of 03/01/2015 and 01/01/2014

For the third cell it should be the difference of 03/01/2015 and 01/01/2014.

So it means in each cell we have to subtract the run date with the very first Posting date.

Could you please provide me the calculation for this requirement.