topic Re: Rolling and ytd calculation in QlikView

Rolling and ytd calculation

saichawan — Wed, 25 Nov 2020 16:16:04 GMT

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 screenshot

Re: Rolling and ytd calculation

saichawan — Thu, 12 Dec 2019 06:31:56 GMT

Sample data

Re: Rolling and ytd calculation

khan_imran — Thu, 12 Dec 2019 10:43:58 GMT

Hi,

Attached is the solution to your problem.

If your query is resolved then Mark it as Solution.

Re: Rolling and ytd calculation

saichawan — Thu, 12 Dec 2019 11:06:11 GMT

hey, thanks a ton for your solution. current year data is matching, but for the last the revnue is suming up for 2 years.

Re: Rolling and ytd calculation

khan_imran — Thu, 12 Dec 2019 11:21:24 GMT

Basis the shared data,if you select Period as CY_Roll_12m then your current month value comes as 50.43 and Last year 53.75

If you select Period as LY_Roll_12m then your current month value comes as 53.75 and Last year 00.00

Re: Rolling and ytd calculation

saichawan — Thu, 12 Dec 2019 11:24:52 GMT

attahment of dashboard. after using the above code

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

Re: Rolling and ytd calculation

khan_imran — Thu, 12 Dec 2019 12:01:09 GMT

Are you using the same dataset? if not then given me all entire data.

Re: Rolling and ytd calculation

saichawan — Thu, 12 Dec 2019 12:16:27 GMT

PFA

dataset and qlickview

Re: Rolling and ytd calculation

saichawan — Thu, 12 Dec 2019 12:23:03 GMT

QVW file

Re: Rolling and ytd calculation

khan_imran — Thu, 12 Dec 2019 13:02:36 GMT

Here is the application with Solution. you did not add the variable.

If this resolved the accept this as solution.

Regards,

Imran K

Re: Rolling and ytd calculation

saichawan — Thu, 12 Dec 2019 13:20:46 GMT

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.

Re: Rolling and ytd calculation

khan_imran — Thu, 12 Dec 2019 13:39:05 GMT

I guess some date formatting issue is at your end.

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

Re: Rolling and ytd calculation

saichawan — Wed, 18 Dec 2019 07:16:51 GMT

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

Re: Rolling and ytd calculation

Brett_Bleess — Wed, 18 Dec 2019 19:20:47 GMT

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