Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
saichawan
Contributor III
Contributor III

Rolling and ytd calculation

Hello,

I need to find rolling and YTD revenue. rolling month starts from september and ends on august. I have two years of data sep-2019 to aug-2018. i have a text object where current year and last revenue will be displayed. so, when i select rolling in the filter, in the text object revenue should displayed as currrent year rolling revenue and last year rolling revenue.

when i'm using the below code to get revenue, the current year revenue is coming up to be zero and last year revenue is getting switched with current year revenue. Attaching the screenshot of the dashboard for your reference.

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

And when i tried changing the code with 

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

With the above formula, rolling current year and rolling last year values are coming out to be same.

 

PFA of sample Data and screenshot3.png

13 Replies
saichawan
Contributor III
Contributor III
Author

Hello sir,

Thank you for helping me out. PFA, we can see the rolling last year revenue is coming out to be 112.72 , which should be 53.74 and rolling current year should be 50.43.

I'm hoping a text obejct, where current year rolling,last year rolling,current year ytd and last year ytd gets displayed. when i select options from view and period.

4.png

khan_imran
Creator II
Creator II

 I guess some date formatting issue is at your end.

 

Again attaching the screenshot & App for you.LY SelectionLY SelectionCy SelectionCy Selection

saichawan
Contributor III
Contributor III
Author

this code is working only for rolling not for ytd. can you please help me out on this, it will be a great help

='Current Year:'& Num((Sum({<week = {">=$(vMinDate)<=$(vMaxdate)"}>} NET_SLS_AMT)/1000000),'##.00')
& ' Last Year:'&num((((Sum({<week = {">=$(vRollingStart)<=$(vRollingEnd)"},Period=>} NET_SLS_AMT)/1000000)
-
Sum({<week = {">=$(vRollingStart)<=$(vRollingEnd)"},Period= {'LYTD'}> } NET_SLS_AMT)/1000000)),'##.00')

Brett_Bleess
Employee
Employee

Here are some Design Blog posts that may be helpful for things:

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

https://community.qlik.com/t5/Qlik-Design-Blog/Dates-in-Set-Analysis/ba-p/1472511

https://community.qlik.com/t5/Qlik-Design-Blog/Accumulations/ba-p/1466581

Here is the base URL for the Design Blog area in case you want to do some further searching yourself:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Sorry I do not have anything better, my post will also kick things back up in the list, so someone else may have another look and offer up something direct, but no promises.  

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.