topic Re: Set analysis syntax error ? in QlikView

Set analysis syntax error ?

swarup_malli — Mon, 14 Nov 2011 16:12:43 GMT

Hi,

The expression below is supposed to find the sum for dates betwen 5/1/2009 and 4/30/2010, here I'm passing the year using a variable,

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 it still shows records whose membership has ended in the current fiscal year ( in the attached application check year 2007, u'll see 4/21/2008 which falls in current fiscal year)

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

Note: I'm attaching a sample application

Thnx

Swarup

Set analysis syntax error ?

m_woolf — Mon, 14 Nov 2011 16:22:28 GMT

The parentheses aren't balanced.

Re: Set analysis syntax error ?

swarup_malli — Mon, 14 Nov 2011 16:47:19 GMT

I corrected the syntax now, still shows records where membership ended in the current fiscal year

Re: Set analysis syntax error ?

m_woolf — Mon, 14 Nov 2011 17:13:07 GMT

Your ENDDATE field is in all caps, but in the expression it is not.

Re: Set analysis syntax error ?

swarup_malli — Mon, 14 Nov 2011 17:23:21 GMT

changed it ,still the same prob persists

Re: Set analysis syntax error ?

m_woolf — Mon, 14 Nov 2011 17:33:45 GMT

Still unbalanced parentheses. Take out right parentheses after +1

Re: Set analysis syntax error ?

swuehl — Mon, 14 Nov 2011 17:35:17 GMT

Ah, you want to exclude those records from taken into account?

Then use as upper limit the year end of CampaignYear:

sum({< ENDDATE = {">=$(=makedate($(vCampaignYearSelected),5,1))<=$(=makedate($(vCampaignYearSelected),12,31))"} >} COMBINEDGIFT)

edit:

Your requirements don't seem to match:

The expression below is supposed to find the sum for dates betwen 5/1/2009 and 4/30/2010. (edit: for fiscal year 2009 ?)

Re: Set analysis syntax error ?

swarup_malli — Mon, 14 Nov 2011 17:53:45 GMT

Stefan

when I choose 2007, my application still shows 3 records , which it shoul'nt because the membership ended in the current fiscal year,

.this can't be my upper limit ...<=$(=makedate($(vCampaignYearSelected),12,31))"}. .... .my fiscal year ends in <=$(=makedate($(vCampaignYearSelected)+1,4,30))"}...

As you suggested in my previous post ,Qlikview sometimes may not correct an expression when there are too many brackets , so in order to overcome that problem, I created 2 new variables for start and end of fical year , and used them in the application but still for 2007 it shows records whose membership ended in the current fiscal year (ie 4/21/2008 which is current fidcal year )

Note: i have attached a sample application below

Thnx

~MS

Re: Set analysis syntax error ?

m_woolf — Mon, 14 Nov 2011 17:58:32 GMT

Doesn't fiscal year 2007 got from 5/1/2007 to 4/30/2008?

Re: Set analysis syntax error ?

swarup_malli — Mon, 14 Nov 2011 19:07:04 GMT

yeh, fiscal year for 2007 - starts from 5/1/2007 and ends on 4/30/2008

Set analysis syntax error ?

swuehl — Mon, 14 Nov 2011 19:22:19 GMT

Swarup, I must admit I am pretty confused.

Those three records show an enddate April 21st 2008, which is a date in your fiscal year 2007, see your previous post.

To me, showing these records seems to be correct if you would like to show all enddates within fiscal year 2007.

Also for the other years, I don't see any problem.

Could you please clarify, why you don't want to show them?

Re: Set analysis syntax error ?

swarup_malli — Mon, 14 Nov 2011 20:01:06 GMT

Stefan,

My bad ,what you said was right, the requirment was to exclude records if they fall within the current year ,

which could have been accomplished with

"sum({<ENDDATE= {">=$(vLastFiscal)"}> } COMBINEDGIFT)"..

But instead of that I was using

= sum({<ENDDATE= {">=$(vFirstFiscal))<=$(vLastFiscal)"}> } COMBINEDGIFT) /* you are right it will show April21 2008 because of "<=$(vLastFiscal)"

condition.

where

VFirstFiscal= =makedate($(vCampaignYearSelected),5,1)

vLastFiscal = =makedate($(vCampaignYearSelected)+1,4,30)

Thank you for bearing with me Stefan, it was a very silly mistake on my part , i should have seen it...

Thank you one more time ,,i got the solution.

Regards

~ MS

Set analysis syntax error ?

swuehl — Mon, 14 Nov 2011 20:16:26 GMT

Swarup,

good to hear that you solved your problem, I am still a little confused, but anyway, I don't need to understand everything.

Have a nice evening / day,

Stefan

P.S. Shouldnt it be only larger than, not larger than / equal (Otherwise April 30 will also be in the range):

"sum({<ENDDATE= {">$(vLastFiscal)"}> } COMBINEDGIFT)"..