how to find variance to prior month and prior year

[shruthibk]

hi all i need to find variance of month and year how can i achieve this  please guide me

requirement is like this

variance to prior month

   month    jan            feb               march            april

             jan-feb       feb-march      march -april     april

variance to prior year

year       2012              2013                2014                  2015

          2012-2013       2013-2014         2014-2015            2015

regards,

shruthi

[sunny_talwar]

Can you share some sample data with your expected output?

[Chanty4u]

hi,

try  lik dis for month and year

Your year keep as variable ( v_year )

apply calculation field as

For current year

if( your_year_field_name=v_year,value_of_field_name,0)

For Previous year

if( your_year_field_name=v_year-1,value_of_field_name,0)

Variance

if( your_year_field_name=v_year,value_of_field_name,0) - if( your_year_field_name=v_year-1,value_of_field_name,0)

[Anonymous]

like this?

Use below() function..

See the attachment

[shruthibk]

Event Type1	2012	2013	2014	2015
a	833,793,780.51	106,461,407.69	465,508,795.21	36,393,275.71
b	1,112,098.36	2,623,347.62	541,617.00	866,395.00
c	78,369,791.13	-18,823,544.81	63,283,277.86	125,362,713.48
d	-20,150,367.89	-28,565,035.93	21,125,790.45	306,796,065.97
e	-39,488.74	21,084.84	18,403.90	0.00
Total	893,085,813.37	61,717,259.41	550,477,884.42	469,418,450.15

[shruthibk]

hi expected output should be like this for variance to prior month

[shruthibk]

alt((sum([Net Amount]))-below(sum([Net Amount])),sum([Net Amount]))  hi i am using this expression which is giving expected output but i need to show latest 4 months or latest 4 years data how can i limit the dimension to only 4 years or 4 months

[shruthibk]

sorry for variance to prior year

[shruthibk]

Hi all i got Expected result using this expression

alt(sum({<[Event Month Year]={"<=$(vOps_CurrentMY) >=$(vOps_PrevMonthMY2)"}>}[Net Amount])-after(sum({<[Event Month Year]={"<=$(vOps_CurrentMY) >=$(vOps_PrevMonthMY2)"}>}[Net Amount])),sum({<[Event Month Year]={"$(vOps_CurrentMY)"}>}[Net Amount]))

this expression is working fine

where variables

VOps_CurrenMY=max(Event Month Year)

vOps_PrevMonthMY2=addmonths(max(Event Month Year)

Now i have to achieve the same requirement for variance to prior quarter and prior YTD

plz guide me

regards,

shruthi