Help needed for passing values dynamically into it ? Using Set analysis

[swarup_malli]

Hi,

Need assistance with the following requirement which  is as follows :

There is a field called EndDate  , which contains the dates on which the membership  ended  for a person .

when year  2007 is selected , I dont  want  records whose Enddates where before 5/1/2007 ( Because  5/1/2007 to 4/30/2008  falls in  fiscal year2007  , and records b4 5/1/2007 fall in the 2006 fiscal year )

,  people whose membership ended in the previous  fiscal year 2006 should be excluded..and when 2008 is selected then exclude records whose Enddates are in 2007 fiscal year.

VcurrentYear is the variable i have created which takes year selected .

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

----------------------------------------------------------------------------------

the expresioin should be something like this

sum({< Enddate should not fall b4 the current selected fiscal year >} LSIndiv.COMBINEDGIFT).

Note: vCampaignYearSelected variable should be present in the set analysis to take the year selected dynamically

I have written the below expression , I've been banging my head all day   ;(  to implement the above requirement into the expression below

This is my orginal expression :

SUM(

{$<INDACCOUNTNUMBER={"=COUNT({1<Techniques.TECHNIQUETYPE={'AF  WLI'},Techniques.CAMPAIGNYEAR={'<=$(=vCampaignYearSelected)'}>}Techniques.TECHNIQUETYPE)>0

"}

,CAMPAIGNYEAR={$(vCampaignYearSelected)}

>}

LSIndiv.COMBINEDGIFT).

Thank you

Swarup

[swarup_malli]

Looking for a solution   something like this

if( Enddate > 4/30/vCampaignYearSelected,

SUM(

{$<INDACCOUNTNUMBER={"=COUNT({1<Techniques.TECHNIQUETYPE={'AF  WLI'},Techniques.CAMPAIGNYEAR={'<=$(=vCampaignYearSelected)'}>}Techniques.TECHNIQUETYPE)>0

"}

,CAMPAIGNYEAR={$(vCampaignYearSelected)}

>}

LSIndiv.COMBINEDGIFT))

or

using only set analysis

   Regards

Swarup

[swuehl · Accepted solution]

Maybe like

sum({< Enddate = {">=$(=makedate($(vCampaignYearSelected),5,1))"} >} LSIndiv.COMBINEDGIFT)

Do you need to take care of the upper limit for Enddate also? What about if Enddate falls between 01/01 to 4/30 of vCampaignYear +1 , i.e. in the same fiscal year?

You could also include above set into your original like:

SUM(

{$<INDACCOUNTNUMBER={"=COUNT({1<Techniques.TECHNIQUETYPE={'AF  WLI'},Techniques.CAMPAIGNYEAR={'<=$(=vCampaignYearSelected)'}>}Techniques.TECHNIQUETYPE)>0

"}

,CAMPAIGNYEAR={$(vCampaignYearSelected)}

,Enddate = {">=$(=makedate($(vCampaignYearSelected),5,1))"}

>}

LSIndiv.COMBINEDGIFT))

Hope this helps,

Stefan

[swarup_malli]

Stefan,

   Thank you!!! , for helping me out one more time,

Regards

SM

[swarup_malli]

Stefan,

I wanted to take care of upper limit for Enddate also, if Enddate falls between 01/01 to 4/30 of vCampaignYear +1 , i.e. in the same fiscal year?

This is wt i came up with , but unfortunately its not working

SUM(

{$<ENDDATE = {">=$(=makedate($(vCampaignYearSelected),5,1) <=$(=makedate($(vCampaignYearSelected+1),4,30)"}

>}

COMBINEDGIFT)

Thanks

Swarup

[swuehl]

It should probably be

... <=$(=makedate($(vCampaignYearSelected)+1,4,30) ...

And take care that you are not limiting e.g. the Year to vCampaignYearSelected in another part of your set expression.

Hope this helps,

Stefan

[swarup_malli]

I tried

sum({< Enddate = {">=$(=makedate($(vCampaignYearSelected),5,1)) <=$(=makedate($(vCampaignYearSelected)+1),4,30))"} >} COMBINEDGIFT)..

but still I get records , where the member ship ended in the current fiscal year...

I'm attaching a sample application, where When i choose the 2007, i still shows records where membership has ended in the currerent fiscal yeear

[swuehl]

I think you now have a closing bracket to much (just after +1 ). Please take care of the brackets since the syntach checker won't signal these mistakes (I believe it has quite problems checking the parts inbetween the double quotes).

And also use ENDDATE instead of Enddate, field names are case sensitive.

Hope this helps,

Stefan

[swarup_malli]

Stefan,

  Thank you for the quick reply ..

Regards

Swarup