topic Year To Date Calculation in QlikView

Year To Date Calculation

Tue, 17 Jul 2012 06:44:52 GMT

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

Re: Year To Date Calculation

Tue, 17 Jul 2012 07:09:50 GMT

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.

Re: Year To Date Calculation

Tue, 17 Jul 2012 07:40:12 GMT

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

Re: Year To Date Calculation

Jason_Michaelides — Tue, 17 Jul 2012 07:56:22 GMT

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

Re: Year To Date Calculation

Tue, 17 Jul 2012 08:34:17 GMT

Assuming you have Year as a field use:

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

Kiran.