Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
saichawan
Contributor III
Contributor III

Rolling and YTD sales calculation from middle of the month

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

 

15 Replies
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

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
saichawan
Contributor III
Contributor III
Author

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')

datt.PNG

datt.PNG

 

Taoufiq

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
saichawan
Contributor III
Contributor III
Author

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:

datt.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
saichawan
Contributor III
Contributor III
Author

do you want me to share the sample data ?

Taoufiq_Zarra

yes

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
saichawan
Contributor III
Contributor III
Author

 
saichawan
Contributor III
Contributor III
Author

Sry. This is the correct data