topic Re: Rolling and YTD calculation in QlikView

Rolling and YTD calculation

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

Hello,

I'm trying to calculate the rolling and ytd revenue, but not from the month of january to december. i need to do it from September 2017 to aug 2018 and sep 2018 to aug 2019. i'm using below code to ge the output. I have a filter called view in which i have two selection rolling and ytd. if i select rolling then the in the revenue text object the output should be current year rolling and last year revenue, if i select ytd from the view filter, then the revenue text object should de displayed as current year YTD and last year ytd revenue. with the below i'm not getting the desired output, the numbers are not correct.

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

Re: Rolling and YTD calculation

saichawan — Mon, 23 Dec 2019 12:32:55 GMT

QVW file

Re: Rolling and YTD calculation

Taoufiq_Zarra — Mon, 23 Dec 2019 12:55:25 GMT

can you please confirm these results.

Between the two dates d1 and d2, which represents the current year, I find 45.89, not 50.4.

I kept your formula:
'Current Year:'& Num((Sum({<week = {">=$(d1) <=$(d2)"}>} NET_SLS_AMT)/1000000),'##.00')

can you give me your feedback on that?

Re: Rolling and YTD calculation

saichawan — Mon, 23 Dec 2019 13:01:06 GMT

total revenue os 104, and current year revenue is 50.4 and last year revenue is 53.7. attaching you the dataset

Re: Rolling and YTD calculation

Taoufiq_Zarra — Mon, 23 Dec 2019 13:05:38 GMT

Sum(NET_SLS_AMT)/1000000 between 01/09/2018 and 01/08/2019 is 50.4 ?

how'd you find it ?

Re: Rolling and YTD calculation

saichawan — Mon, 23 Dec 2019 13:10:36 GMT

In the dataset, there a column called period and if i select CYRoll12m then you see Net_sls_amt as 50.4 sep 2018 to aug 2019 and if i select LYRoll12m then the net_sls_amt is 53.7 sep 2017 to aug 2018.

Re: Rolling and YTD calculation

Taoufiq_Zarra — Mon, 23 Dec 2019 13:17:50 GMT

I can't help you if you don't help me understand.

Sum(NET_SLS_AMT)/1000000 between 01/09/2018 and 01/08/2019 is 45.86 not 50.4 ?

Re: Rolling and YTD calculation

saichawan — Mon, 23 Dec 2019 13:20:58 GMT

sorry my bad, yes it is 45.86 i was looking at other dataset not shared one. really sorry for this.

Re: Rolling and YTD calculation

Taoufiq_Zarra — Mon, 23 Dec 2019 13:24:07 GMT

qvw file

Re: Rolling and YTD calculation

saichawan — Mon, 23 Dec 2019 13:24:04 GMT

haha.

we got the right numbers for current year

Re: Rolling and YTD calculation

Taoufiq_Zarra — Mon, 23 Dec 2019 13:25:10 GMT

49.11 for last year ?

Re: Rolling and YTD calculation

saichawan — Mon, 23 Dec 2019 13:27:46 GMT

yes, correct. 49.11 for the last year

Re: Rolling and YTD calculation

Taoufiq_Zarra — Mon, 23 Dec 2019 13:45:31 GMT

Re: Rolling and YTD calculation

Taoufiq_Zarra — Mon, 23 Dec 2019 13:51:12 GMT

add variables :

d1=Date(Makedate(Year(Max(week))-1,09,01),'DD/MM/YYYY')

d2=Date(Makedate(Year(Max(week)),08,01),'DD/MM/YYYY')

d3=Date(Makedate(Year(Max(week))-2,09,01),'DD/MM/YYYY')

d4=Date(Makedate(Year(Max(week))-1,08,01),'DD/MM/YYYY')

then change revenue text by

='Current Year:'& Num((Sum({<week = {">=$(d1) <=$(d2)"}>} NET_SLS_AMT)/1000000),'##.00') & ' Last Year:'&num((((Sum({<week = {">=$(d3)<=$(d4)"},Period=>} NET_SLS_AMT)/1000000) - Sum({<week = {">=$(d3)<=$(d4)"},Period= {'LYTD'}> } NET_SLS_AMT)/1000000)),'##.00')

Re: Rolling and YTD calculation

saichawan — Mon, 23 Dec 2019 13:58:38 GMT

where should i create these variables? a text object

d1=Date(Makedate(Year(Max(week))-1,09,01),'DD/MM/YYYY')

d2=Date(Makedate(Year(Max(week)),08,01),'DD/MM/YYYY')

d3=Date(Makedate(Year(Max(week))-2,09,01),'DD/MM/YYYY')

d4=Date(Makedate(Year(Max(week))-1,08,01),'DD/MM/YYYY')

Re: Rolling and YTD calculation

Taoufiq_Zarra — Mon, 23 Dec 2019 14:03:13 GMT

then

Re: Rolling and YTD calculation

saichawan — Mon, 23 Dec 2019 14:17:51 GMT

Hi,

swap1546@gmail.com is my mail id.

the number for rolling is coming correct, but when i select YTD from view filter the numbers are not correct. attaching you the screenshot.

Re: Rolling and YTD calculation

Taoufiq_Zarra — Mon, 23 Dec 2019 14:23:26 GMT

what are the correct values?

Re: Rolling and YTD calculation

saichawan — Tue, 24 Dec 2019 05:48:57 GMT

Hi,

Thanks a ton for guiding me to get the desired output. i just have a small problem here. With the below and making variable overview. my YTD last year revenue is coming upto be zero. Can you please through a light on this.

Attaching you the screenshot of the same.

='Current Year:'& Num((Sum({<week = {">=$(d1) <=$(d2)"}>} NET_SLS_AMT)/1000000),'##.00')
& ' Last Year:'&num((((Sum({<week = {">=$(d3)<=$(d4)"},Period=>} NET_SLS_AMT)/1000000)
-
Sum({<week = {">=$(d3)<=$(d4)"},Period= {'LYTD'}> } NET_SLS_AMT)/1000000)),'##.00')

d1=Date(Makedate(Year(Max(week))-1,09,01),'DD/MM/YYYY')

d2=Date(Makedate(Year(Max(week)),08,30),'DD/MM/YYYY')

d3=Date(Makedate(Year(Max(week))-2,09,01),'DD/MM/YYYY')

d4=Date(Makedate(Year(Max(week))-1,08,30),'DD/MM/YYYY')

This will be big help for me.

Thanks

Sai