Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
saichawan
Contributor III
Contributor III

YTD calculation

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

 

21 Replies
saichawan
Contributor III
Contributor III
Author

QVW file

saichawan
Contributor III
Contributor III
Author

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

 

Capture.PNG

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
saichawan
Contributor III
Contributor III
Author

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

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

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
saichawan
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

DATA

Taoufiq_Zarra

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

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
saichawan
Contributor III
Contributor III
Author

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.

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉