Rolling and ytd calculation

[saichawan]

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

[anushree1]

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

[saichawan]

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

sai

[anushree1]

Sorry i am not sure what the desired output is here

[saichawan]

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 year.

Attaching the screenshot fyi and also the dataset.

Thanks

Sai

[Brett_Bleess]

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