Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Year To Date Calculation

Hi All,

I have some sales data for which i want to calculate YTD. The months are expressed by numbers like Jan =1, Feb =2..and .so on. I want to calculate the total sales for the period April 2011 - March 2012. How do i go about it in my sum statement?

Regards

Anirban

4 Replies
Not applicable
Author

Add a new field in the script to give fiscal year. Something like

if(month<=3,Year-1&'-'Year,Year&'-'&Year+1) as FiscalYear.

Adding the FiscalYear in the list box would do the job.

Kiran Rokam.

Not applicable
Author

Hi Kiran,

Thanks for the reply. I wanted to ask you isnt there anything we could do without touching the back end script? I mean within the table can we use set analysis and get the job done.

Your views would be appreciated

Thanks

Anirban

Jason_Michaelides
Luminary Alumni
Luminary Alumni

You should define the Fiscal year in the script, then use Set Analysis in the UI to sum values for that year.

In the script:

Year(YearStart(Date,0,4)     AS     FiscalYear

In the UI:

Sum({<Date=,Month=,Year=,FiscalYear=P()>} Sales)

This will sum all sales for the currently selected/possible FiscalYear(s)

Hope this helps,

Jason

Not applicable
Author

Assuming you have Year as a field use:

=Sum(if(if(month<=3,Year-1&'-'Year,Year&'-'&Year+1)='2011-2012',Sales,0))

Kiran.