topic Re: YTD calculation in QlikView

YTD calculation

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

Hi,

I have the code below which calculates the current year rolling sales and lasy year rolling sales. i'm not able to do the same for ytd calculation in the same code. i have filter of view with option of (rolling and ytd). if select rolling from the filter i can sales in text object with current year rolling sales and last year rolling sales. I want the same to be calculated for YTD.

Attaching you the QVW file and aslo the code for rolling calculation.

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

Thanks

Re: YTD calculation

Anil_Babu_Samineni — Wed, 18 Dec 2019 12:54:52 GMT

What purposely you are creating many duplicate threads? Original Thread here

Re: YTD calculation

sunny_talwar — Wed, 18 Dec 2019 12:58:27 GMT

Would you be able to provide a dashboard where we can see just the objects we are looking at and with a single tab? Right now, everytime I open this dashboard I have to spend time looking to see if I can find what you are referring to or ask you what it is. In general it will be easy for us to help if you do certain things to make it easy for us to help you such as

1) Provide the expected output based on selections and no selections

2) Provide just the charts/list boxes that we need to look at

Re: YTD calculation

saichawan — Wed, 18 Dec 2019 12:58:59 GMT

it happend through mistake

Re: YTD calculation

saichawan — Wed, 18 Dec 2019 13:02:45 GMT

sorry for the inconvinence.

in the summary tab,i have a revenue text object. if i select rolling from the filter the values are getting as per requirement. but when i select YTD from filter . only the current year values are getting displaced not the last year values.

Re: YTD calculation

saichawan — Wed, 18 Dec 2019 13:04:33 GMT

QVW file

Re: YTD calculation

sunny_talwar — Wed, 18 Dec 2019 13:13:42 GMT

What would you want to see when you select YTD for Last Year?

Re: YTD calculation

saichawan — Wed, 18 Dec 2019 13:21:12 GMT

Current year YTD and last year YTD. Right I'm getting values for rolling. If i select rolling from the view filter i get current year rolling revenue and last year rolling revenue. I'm hoping the same for YTD. i select YTD from view filter then in the text object of revenue. It should be displayed as current year YTD revenue and last year YTD revenue.

Re: YTD calculation

sunny_talwar — Wed, 18 Dec 2019 14:07:34 GMT

I have been trying really hard to help, but I am not able to get the information from you to help. Unless you can clearly give me what you want, I will have to let someone else help you out here.

Best,
Sunny

Re: YTD calculation

saichawan — Wed, 18 Dec 2019 14:20:31 GMT

i'm really sorry for this miss coummincation.

now in the dashboard, i have three tabs. i'm concerned about the summary tab. In the summary i have a text object called revenue. as per code of the text object revenue, if select rolling from the view filter and Cy_rolling_12m from period fillter. i get current year revenue and last year revenue in the revenue text object. now if i select YTD from view filter you can see that last year revenue is always zero. i'm looking for the same output as the one when i select rolling from the view filter but for YTD revenue. i select YTD from the view filter and CYTD or LYTD from period filter. i should be getting what is the current year YTD and last year YTD in the revenue text object

Thanks

Re: YTD calculation

saichawan — Wed, 18 Dec 2019 14:37:39 GMT

QVW file

Re: YTD calculation

saichawan — Wed, 18 Dec 2019 16:02:15 GMT

was my last message clear,i gotta deliver by tomorrow. any help will do wonder for me. Do you want me to share qvw file?

Thanks

Sai

Re: YTD calculation

Taoufiq_Zarra — Thu, 19 Dec 2019 13:09:37 GMT

@saichawan

just a little note , if I understand correctly.
The basis of the calculations here is the two variables vMindate and vMaxdate.
which displays the following values :

while in Revenue you search for 'current year' and 'last year'.

when you do vMindate it's the minimum value which is not necessarily the current year, but rather the minimum of all dates in the weeks field.

and in Last year there is a value minus another one with the same logic of min and max so two similar values with a minus it gives directly a 0 in Last Year

Re: YTD calculation

saichawan — Thu, 19 Dec 2019 13:18:17 GMT

Hi ,

Thank you for your extended support, i had a review meeting today for the dashboard. they dont want period as a filter in the dashoard. there should be only filter of view consisting rolling and ytd. Now suppose i select rolling from view filtre then in the text object of revenue. i should get rolling current year revene and rolling last year revenue and if i select YTD from view filter then in the revenue text object it should be current year YTD revenue and last year YTD revenue. To the question you asked i think the logic doesnt fit. This is the requriment, can you help me on this please.

Re: YTD calculation

Taoufiq_Zarra — Thu, 19 Dec 2019 13:39:54 GMT

I don't know if the attached version can help you.
I have replaced the vMin and vMax by current year and last year

Re: YTD calculation

saichawan — Thu, 19 Dec 2019 14:38:57 GMT

with the version that i have both the view and period as filter. but now i need select only from view filter to get my rolling current year and last year sales aslo current ytd and last year ytd.

Attaching you the new refrence also the data

Re: YTD calculation

saichawan — Thu, 19 Dec 2019 14:40:00 GMT

DATA

Re: YTD calculation

Taoufiq_Zarra — Thu, 19 Dec 2019 15:24:40 GMT

Is this a new need? Is the original problem solved?

Re: YTD calculation

saichawan — Thu, 19 Dec 2019 15:27:47 GMT

Yes, but they dont want the filter of period, they want me to calcualte it from view filter itself.

Re: YTD calculation

Taoufiq_Zarra — Thu, 19 Dec 2019 15:31:53 GMT

if it's solved, I think it will be better for other users to mark the solution and close the topic, then open a new one with the new need, and it will be a pleasure to help you on this point.