Creating a Cumulative LY vs CY

[#1]

Hi,

I am trying to generate a graph with Cumulative values by month for CY vs LY.

For CY i am using below and it works fine(Working)

rangesum(above(

Sum({< DateNum={">=$(=Num(YearStart(Today(),0,10)))<$(=Num((Today())))"}

,OpportunityStageName ={'Closed Won'}>} [Weighted_Booking__Amt]/1000000),0,rowno()))

For LY I am not getting the rite result(Not Working)

RangeSum(Above

(Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Today()-365,0,10)))<$(=Num(Yearend(Today()-365,0,10)))"}

,OpportunityStageName ={'Closed Won'} >} Weighted_Booking__Amt/1000000)),0,RowNo())

Please help.

[jolivares]

First post an example of your data for helping you... Explain what you need specificaly

[petter · Accepted solution]

Your brackets/parantheses seems to be misplaced in your second expression:

RangeSum(Above(

Sum({< DateNum={">=$(=Num(YearStart(Today(),0,10)))<$(=Num((Today())))"}

,OpportunityStageName ={'Closed Won'}>} [Weighted_Booking__Amt]/1000000),0,rowno()))

For LY I am not getting the rite result(Not Working)

RangeSum(Above(

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Today()-365,0,10)))<$(=Num(Yearend(Today()-365,0,10)))"}

,OpportunityStageName ={'Closed Won'} >} Weighted_Booking__Amt/1000000)),0,RowNo())

After the 1000000 in the first expression you have one of them. In the second expression there are two of them.

At the end of the expression there is two versus one...

I have highlighted in red and black ....

[jagan]

Hi,

Try this expression

Rangesum(Above

(Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Today(),-1,10)))<$(=Num(Yearend(Today(),-1,10)))"}

,OpportunityStageName ={'Closed Won'} >} Weighted_Booking__Amt/1000000),0,RowNo()))

Issue with Paranthesis, correct it.

Regards,

Jagan.

[#5]

Thanks Peter and Jagan...It was indeed Paranthesis.

[jagan]

Hi,

Also never use -365 to get previous year, for leap year it is 366 days.  Some times there may be issue, so use below expression to get YearStart of previous year.

YearStart(Today(),-1,10).

Regards,

jagan.

[#7]

Thanks for the tip Jagan.