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 calculation

Hello,

I'm trying to calculate the rolling and ytd revenue, but not from the month of january to december. i need to do it from September 2017 to aug 2018 and sep 2018 to aug 2019. i'm using below code to ge the output. I have a filter called view in which i have two selection rolling and ytd. if i select rolling then the in the revenue text object the output should be current year rolling and last year revenue, if i select ytd from the view filter, then the revenue text object should de displayed as current year YTD and last year ytd revenue. with the below i'm not getting the desired output, the numbers are not correct.

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

1 Solution

Accepted Solutions
Taoufiq_Zarra

what are the correct values?

Regards,
Taoufiq ZARRA

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

(you can mark up to 3 "solutions") 😉

View solution in original post

18 Replies
saichawan
Contributor III
Contributor III
Author

QVW file

Taoufiq_Zarra

can you please confirm these results.

Between the two dates d1 and d2, which represents the current year, I find 45.89, not 50.4.

Capture.PNG

I kept your formula:
'Current Year:'& Num((Sum({<week = {">=$(d1) <=$(d2)"}>} NET_SLS_AMT)/1000000),'##.00')

Capture.PNG

can you give me your feedback on that?

 

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

total revenue os 104, and current year revenue is 50.4 and last year revenue is 53.7. attaching you the dataset 

Taoufiq_Zarra

Sum(NET_SLS_AMT)/1000000 between  01/09/2018 and 01/08/2019 is 50.4 ?

how'd you find it ?

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

In the dataset, there a column called period and if i select CYRoll12m then you see Net_sls_amt as 50.4 sep 2018 to aug 2019 and if i select LYRoll12m then the net_sls_amt is 53.7 sep 2017 to aug 2018.

 

Taoufiq_Zarra

I can't help you if you don't help me understand.

Capture.PNG

Sum(NET_SLS_AMT)/1000000 between 01/09/2018 and 01/08/2019 is 45.86 not 50.4 ?

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

sorry my bad, yes it is 45.86 i was looking at other dataset not shared one. really sorry for this.

Taoufiq_Zarra

qvw file

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

haha.

we got the right numbers for current year