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

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

swarup_malli — Sun, 13 Nov 2011 18:12:15 GMT

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

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

swarup_malli — Sun, 13 Nov 2011 18:44:09 GMT

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))

using only set analysis

Regards

Swarup

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

swuehl — Sun, 13 Nov 2011 18:50:43 GMT

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

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

swarup_malli — Sun, 13 Nov 2011 21:13:12 GMT

Stefan,

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

Regards

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

swarup_malli — Mon, 14 Nov 2011 15:48:56 GMT

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

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

swuehl — Mon, 14 Nov 2011 16:25:12 GMT

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

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

swarup_malli — Mon, 14 Nov 2011 16:35:46 GMT

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

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

swuehl — Mon, 14 Nov 2011 16:57:50 GMT

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

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

swarup_malli — Mon, 14 Nov 2011 17:07:35 GMT

Stefan,

Thank you for the quick reply ..

Regards

Swarup