Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Rolling and ytd calculation

Hello,

I need to calculate the rolling and ytd revenue generation in qlickview. I have a table of rolling and ytd in qlickview dashboard. This rolling and ytd columns i have created in excel. when i'm using a formula, the answer is not coming right. can you please help me out on this.

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

 

Thanks

Sai

5 Replies
Highlighted
Specialist II
Specialist II

Please try as below:

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

 

Highlighted
Contributor III
Contributor III

Hi Anushree,

Thanks for your time.

It is not correct, i'm attching the excel file also the screenshot of the dashboard for a better understanding.

 

Thanks

saiimage1.png

Highlighted
Specialist II
Specialist II

Sorry i am not sure what the desired output is here

Highlighted
Contributor III
Contributor III

So, i need to calculate rolling current year revenue, rolling last year revnue and current ytd,last year ytd. i have created a text object where i have current year revenue and last year revenue. and filter called view, where view is the column name in excel with rolling and ytd as observation in view column. period as a other column in the excel where observation as cy_roll_12,ly_roll_12,lytd,cytd as observation in the that period. so when i select rolling from view filter and cy_roll_12 from period filter, i need to get the value of rolling revene for current year and last yearimage1.png.

Attaching the screenshot fyi and also the dataset.

 

Thanks

Sai

Highlighted
Digital Support
Digital Support

Here are some Design Blog posts that may be of use for your use case:

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

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

Here is the base Design Blog URL in case you wish to search further:

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

Hopefully this will assist you in getting a solution that will work for you.  If you would like more direct help, I would venture you will need to attach the QVW file, or a sample one with the like data model and chart etc. in order for folks to be able to better dig into things.

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.