Report Requirement

building reports webintelligence
1

Hi Team

I got a requirement like , I need to divide the amount for the current year with previous year which falls on same date and then subtract 1 from it.

For Eg: data for 1/1/2005 devided by data for 1/1/2004 minus 1 in the same way data for 2/1/2005 divided by data for 2/1/2004 minus 1.

I have attached a excel sheet for more details.

Can anyone pls help me on this ???

Thanks in Advance
Anusha
Requirement.xls (25.0 KB)

Asmitha :uk: (BOB member since 2008-06-06)

2

Hi,

Did you check with Previous() function?

GowthamSen :india: (BOB member since 2006-08-31)

3

yeah , I checked using Previous function, but of no use… :frowning:

Asmitha :uk: (BOB member since 2008-06-06)

4

Hi Anu,

Can you provide us how you used previous function in your report…!!

Thanks

BOCP (BOB member since 2007-07-02)

5

Hi

I just tried using previous(year) it is returning me the previous cell value into the next cell. I could not be able to use it to get 2003 data where year is 2004.

If you have any idea can you please share with me?

Thanks
Anusha

Asmitha :uk: (BOB member since 2008-06-06)

6

That z strange.

Try this:

 Var= ([Rev] where [fiscal Yr]=[current Yr])/([Rev] where [Fiscal Yr]=Previous[Current Yr])

I didn’t tried it, If you face any problem just post it.

Thanks
kishoreykumar@gmail.com

BOCP (BOB member since 2007-07-02)

7

Hey

Can you pls explain this ?

Thanks
Anu

Asmitha :uk: (BOB member since 2008-06-06)

8

Do the dates vary or just the month and year(Is it always the first of every month)?

cyberdude :india: (BOB member since 2007-02-05)

9

No dates wont vary. 1 will be the first date of every month. but there is a chance of missing months in between. we might not have data for everymonth.

Asmitha :uk: (BOB member since 2008-06-06)

10

Hey Anusha,

Try like this:



=ToNumber(FormatNumber(([Rev]))/Previous([Rev]);"#.0"))

Change the formula as per your requirement…

Hope this helps you…!!

Thanks
kishoreykumar@gmail.com

BOCP (BOB member since 2007-07-02)

11

Thanks BOCP.

Can you please make me understand what “#.0” means…

I tried this formulae, however it is giving me the wrong values. it is just taking the previous (from previous cell, not from previous year) of REV value.

Thanks
Anusha

Asmitha :uk: (BOB member since 2008-06-06)

12

#.0 for formatting the result.

It should work , don’t directly use it , change it as per your requirement…

You need to do it by creating multiple variables…



[Claim Amt/Hdct] where ([Date]='create formula here to identify month and day equal ' /Previous[Claim Amt/Hdct]

try like this , I’ll try to give you full formula ASAP.

Thanks

BOCP (BOB member since 2007-07-02)