Set analysis to exclude values from previous fiscal year ?
,
the req is as follows :
the dates mentioned below
5/1/2007 to 4/30/2008 falls in fiscal year2007
5/1/2008 to 4/30/2009 falls in fiscal year2008
5/1/2008 to 4/30/2009 falls in fiscal year2009
5/1/2009 to 4/30/2010 falls in fiscal year2010
My requirement is :
There is a field called EndDate , which contains the dates on which the membership ended for a person .
Requirement is when i choose 2007, I only want records whose Enddates fall in the fiscal year 2007 , people whose membership ended in the previous fiscal year 2006 should be excluded.
I have written the below expression , I've been banging my head all day ;( to implement the above requiremnt into the expression below
Might be a bit tricky to evaluate without seing your datat model. Please attach a sample QVW, including the object where you are adding the expression.
1. Add the fiscal year to your EndDate data table, e.g. EndDateFiscalYear
2. Use a variable to keep track of your selected calendar year,
e.g. vSelectedYear = GetFieldSelections(CalendarYear)
3. Use the variable to pick out your fiscal end dates; Sum( { $<EndDateFiscalYear={'$(vSelectedYear)'}>} LSIndiv.COMBINEDGIFT)
It is really hard and time consuming to assume what will work in your scenario, so a sample of the QVW including your datat model would really be needed in order to give a more exact answer.
using yearstart and yearend might help you. You can use those to define the fiscal year, and then do something like enddate>=yearstart and enddate<=yearend.
What you could do in order to limit the QVW size, is to make as many selection as possible that still will present the problem you have encountered. Then you simply save the file as a new file and reduce it to only contain the possible values based on your current selection.
