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.image2.png

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

 

6 Replies
Anonymous
Not applicable

your syntax for current year results in week >= 1.9.2019 <=31.8.2020 
for last Year results in week>=1.9.2018<=31.8.2019

is that what you want? your current year starts sept 2019 and end aug 2020?
If yes: what format is week?

Does your next textobject retail quantity show correct numbers? If yes, copy that expression and adapt it to your revenue

saichawan
Contributor III
Contributor III
Author

I'm attaching you the sample data, if that helps you in understanding 

saichawan
Contributor III
Contributor III
Author

rolling current year will be- from 2018-sep to 2019- aug and for rolling last year, 2017-sep to 2018-aug

Anonymous
Not applicable

so change to

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

saichawan
Contributor III
Contributor III
Author

Hey,

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

PFA>3.png

Brett_Bleess
Employee
Employee

Check the following Design Blog link regarding the rolling component:

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

Here is another one on Set Analysis that may be helpful:

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

Here is the base URL for the Design Blog area, so you can search on your own if you wish to dig for additional posts, there are hundreds of posts in this area from our internal experts that are mostly how-to related, so should be a good place to look for ideas in the future.

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

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.