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

this is normal since the[Trading Week End Date] field only contains the year.

uses another field with the complete date to be able to filter by month.

I changed this field to [Week] and it doesn't return 0

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

i tried with full date format, the numers are not adding up the excel that i shared and when i selecting rolling as a filter in qlickview. the current year is zero and last year is 50.43Untitled3.png

Taoufiq_Zarra

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

I'll tell you what excalty i need and where i'm facing the problem. I have the column in the excel of view (rolling/ytd). i have made a filter option in qlickview, and made a text object to to know the rolling and ytd sales for current year and last year. I'm thankfull for your help sir, can you help me with the problem.i'm new to the qlickview. I think i have shared the excel file, do you want me to share the QVW file too?

 

Brett_Bleess
Former Employee
Former Employee

Have a look at the following Design Blog post, might give you some additional ideas on approaching this one:

https://community.qlik.com/t5/Qlik-Design-Blog/Fiscal-Year/ba-p/1472103

https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
saichawan
Contributor III
Contributor III
Author

HI,

with this formula, if i select rolling from the filter then the current year revenue becomes zero and last year revenue is getting switched with current year revenue. attaching you the screenshot and the data file for better understanding.

='Current Year:'& Num(Sum({<[week] = {">=$(=MakeDate(Year(Today()),9,1) )<=$(=MakeDate(Year(Today())+1,8,30) )"}> } NET_SLS_AMT/1000000),'##.00')
& ' Last Year:'&Num(Sum({<[week] = {">=$(=MakeDate(Year(Today())-1,9,1) )<=$(=MakeDate(Year(Today()),8,30) )"}> } NET_SLS_AMT/1000000),'##.00')

 

Thanks

Sairevenue.png