YTD calculation

[saichawan]

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

[saichawan · Accepted solution]

QVW file

[saichawan]

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

[Taoufiq_Zarra]

@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

[saichawan]

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. 

[Taoufiq_Zarra · Accepted solution]

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

[saichawan]

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

[saichawan]

DATA

[Taoufiq_Zarra]

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

[saichawan]

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

[Taoufiq_Zarra]

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.