Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
swarup_malli
Specialist
Specialist

Set analysis syntax error ?

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

12 Replies
swuehl
MVP
MVP

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?

swarup_malli
Specialist
Specialist
Author

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

swuehl
MVP
MVP

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