Rolling and YTD sales calculation from middle of the month

[saichawan]

Good day!

I trying to calculate the rolling and ytd sales, but not from the month of january to december. i need to do it from july to june.  ='Current Year:'& Num(Sum({<[Trading Week End Date] = {'$(=Year(Today()))'}>} NET_SLS_AMT/1000000),'##.00')& ' Last Year:'&Num(Sum({<[Trading Week End Date] = {'$(=Year(Today())-1)'}>} NET_SLS_AMT/1000000),'##.00') I'm using this formula, but sales is reflecting from january to december. Can you please help me with this problem to get rolling & ytd sales from july month to june month

Thanks

[Taoufiq_Zarra]

Hi,

in your scripts you don't refer to June and July.

I suggest you change your scripts by the functions:

for the current year

{">=$(=MakeDate(Year(Today()),7,1) )<=$(=MakeDate(Year(Today())+1,6,1) )"}> }

for the previous year

{">=$(=MakeDate(Year(Today())-1,7,1) )<=$(=MakeDate(Year(Today()),6,1) )"}> }

so your script becomes:

='Current Year:'& Num(Sum({<[Trading Week End Date] = {">=$(=MakeDate(Year(Today()),7,1) )<=$(=MakeDate(Year(Today())+1,6,1) )"}> }>} NET_SLS_AMT/1000000),'##.00')& ' Last Year:'&Num(Sum({<[Trading Week End Date] = {">=$(=MakeDate(Year(Today())-1,7,1) )<=$(=MakeDate(Year(Today()),6,1) )"}> } NET_SLS_AMT/1000000),'##.00')

Cheers

Taoufiq

[saichawan]

Not working, it throws a error in expression: ')'expected 

[Taoufiq_Zarra]

Hi,

maybe an oversight

Try it with this correction, for me it works.

='Current Year:'& Num(Sum({<[Trading Week End Date] = {">=$(=MakeDate(Year(Today()),7,1) )<=$(=MakeDate(Year(Today())+1,6,30) )"}> } NET_SLS_AMT/1000000),'##.00')
& ' Last Year:'&Num(Sum({<[Trading Week End Date] = {">=$(=MakeDate(Year(Today())-1,7,1) )<=$(=MakeDate(Year(Today()),6,30) )"}> } NET_SLS_AMT/1000000),'##.00')

Taoufiq

[saichawan]

it is not throwing an error now, but the values are zero

[Taoufiq_Zarra]

can you send me an example of the data source?

for me:

[saichawan]

do you want me to share the sample data ?

[Taoufiq_Zarra]

yes

[saichawan]

Sry. This is the correct data