Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
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
Master II
Master II

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
Master II
Master II

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
Master II
Master II

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
Master II
Master II

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