topic Re: Current Fuscal year Sales in QlikView

Current Fuscal year Sales

alec1982 — Wed, 22 Oct 2014 18:13:29 GMT

hi guys,

I have a table with

Sales ID Payment Date Fiscal year Sales

knowing that the Fiscal Year starts on april 1 of each year and ends on march 31 of the next year and we are currently in 2015 FY.

How can I do sum of current FY sales

Re: Current Fuscal year Sales

Anonymous — Wed, 22 Oct 2014 18:27:01 GMT

Create a Calendar table that maps calendar date to fiscal date, then use the fiscal date fields in your report.

We do this all the time - more than calendar dates.

Re: Current Fuscal year Sales

alec1982 — Wed, 22 Oct 2014 18:29:54 GMT

Thank you for your quick answer. I dont have the ability to create fiscal calendar here.

What i really want is a text box with the sum of sales for current fiscal year giving that i have the FY year on my data and the actual payment date and the first and last days in the fy

Re: Current Fuscal year Sales

Anonymous — Wed, 22 Oct 2014 18:33:21 GMT

Try putting this in the text box's expression:

=sum({$<[Fiscal Year]={'$(=Max({1}[Fiscal Year]))'}>}Sales)

Re: Current Fuscal year Sales

cspencer3 — Wed, 22 Oct 2014 18:33:31 GMT

You can also use the InYearToDate() function. Pass it the date you are looking to check if it is in the current YTD, and the 'First month of year' argument (in your case 4 for April). Then just do: SUM(if(InYearToDate(DateField,Today(),0,4),Sales)

InYearToDate (date, basedate , shift [, first_month_of_year = 1])

Re: Current Fuscal year Sales

alec1982 — Wed, 22 Oct 2014 18:36:26 GMT

Hi,

This doesn't work as there are future sales scheduled for next FY year and using Max will pick up the next year

Re: Current Fuscal year Sales

alec1982 — Wed, 22 Oct 2014 18:37:16 GMT

Thank you but not sure how can i use this function for to sum Sales for current FY year.. Can you please give me more details or expression that I can use.

Re: Current Fuscal year Sales

cspencer3 — Wed, 22 Oct 2014 18:55:40 GMT

I used this exact line when trying with some dummy data in a dashboard:

=SUM(if(InYearToDate(PaymentDate,Today(),0,4),Sales) )

Thus, if the PaymentDate is in the YTD (Adjusting 4 months for Fiscal years) then it will be true and return the sales amount. Thus it is then summed up by the SUM function. I tested this to get only PaymentDates that are in the current fiscal year, and not after today(). Therefore it should give me 200 in sales, and thus it did. Does this make sense, and/or applicable in your situation? What does your data look like?

Dummy Data:

SalesID, PaymentDate, FiscalYear, Sales

1, 5/1/2014, 2014, 100

2, 1/1/2014, 2013, 200

3, 2/1/2014, 2013, 300

4, 6/5/2014, 2014, 100

5, 1/1/2013, 2012, 200

6, 3/2/2015, 2014, 200

Re: Current Fuscal year Sales

cspencer3 — Wed, 22 Oct 2014 18:57:22 GMT

I also marked down 2014 as the fiscal year, but apparently it is supposed to be 2015. In my line of work the Fiscal year starts 10/1, not 4/1. Just noticed that. But the concept is the same. That will be the Current Year to date, fiscal year calculation.

Re: Current Fuscal year Sales

alec1982 — Wed, 22 Oct 2014 18:57:45 GMT

Hi Charles,

this doesn't work on my case as I have future dates scheduled for next year on the table and by doing the above all data from the next year will be summed..

Re: Current Fuscal year Sales

cspencer3 — Wed, 22 Oct 2014 19:01:36 GMT

It should not be summed. with the InYearTodate function you can tell it when to stop. In this case it is by using the Today() function, which says "Is the date in the current fiscal year, up to today?" if so then return true. If you wanted to stop at the end of the fiscal year, you could put in the last day of the current fiscal year. This would only return sales for the current fiscal year, and not the next year. I threw an example date in the data shown previously and the sum of those sales did not get added into my total.

Re: Current Fuscal year Sales

maternmi — Wed, 22 Oct 2014 19:06:06 GMT

Hi,

your table have a column fiscal year, so I guess you see there only years like 2012, 2013 or 2015. If yes then try to use following

sum({$<([Fiscal year]='2015')}>}Sales) if you put the field "fiscal year" in a list box you should use the following formula sum({$<[Fiscal year]='*')}>}Sales), depending which year you choose you will get the sum of this year.

Michael

Re: Current Fuscal year Sales

alec1982 — Wed, 22 Oct 2014 19:08:16 GMT

Thank you for your answer.. the request that i have is presenting the current fiscal sales without clicking on the list box or hard code the FY year name within set analysis

Re: Current Fuscal year Sales

alec1982 — Wed, 22 Oct 2014 19:15:23 GMT

Thank you Charles but it doesnt work.. how can we pass the end of the fiscal year here..as for today's date the last date of the current fiscal year 3/31/2015 and starts on 4/1/2014 and the number of the year is 2015..

We shouldn't hard code the date so how can i pass the last date dynamically?

Best,

Alec

Re: Current Fuscal year Sales

Anonymous — Wed, 22 Oct 2014 19:21:36 GMT

You could try this. If Month(Today()) > 3 - April or greater, then add 1 to the current year.

=sum({$<[Fiscal Year]={'$(=if(Month(Today())>3,Year(Today())+1,Year(Today())))'}>}Sales)

Re: Current Fuscal year Sales

cspencer3 — Wed, 22 Oct 2014 19:30:39 GMT

instead of using the today() function to pass today as the 'last day in the YTD fiscal year' use the function YearEnd(). Thus you would be finding the End of the current year if you use YearEnd(Today(), 0, 4 )

This says the YearEnd = 3/31/15

So substitute the today() in the line above with: YearEnd(Today(), 0, 4 )

THus if the date is within fiscal year 2015 it will grab the sales amount and sum them up .Thus it is not hard coding, and when you then enter Fiscal Year 2016, it will automatically grab 2016 data. If you want to show 2015s data again somewhere just use:

SUM(if(InYearToDate(PaymentDate,YearEnd(Today(), -1, 4 ),-1,4),Sales) )

Notice the -1s which tells QV to check the Previous Year start and Previous Year End.

Re: Current Fuscal year Sales

cspencer3 — Wed, 22 Oct 2014 19:31:33 GMT

In conclusion, this should work depending on your application:

SUM(if(InYearToDate(PaymentDate,YearEnd(Today(), 0, 4 ),0,4),Sales) )

Re: Current Fuscal year Sales

alec1982 — Wed, 22 Oct 2014 19:40:25 GMT

Thank you... this is it!

Re: Current Fuscal year Sales

cspencer3 — Wed, 22 Oct 2014 19:50:44 GMT

I am getting the same result. Steve's looks much cleaner though. If you have the Fiscal Year field i guess you might as well use it. Good work!