Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
The parentheses aren't balanced.
I corrected the syntax now, still shows records where membership ended in the current fiscal year
Your ENDDATE field is in all caps, but in the expression it is not.
changed it ,still the same prob persists
Still unbalanced parentheses. Take out right parentheses after +1
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 ?)
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)
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
Doesn't fiscal year 2007 got from 5/1/2007 to 4/30/2008?
yeh, fiscal year for 2007 - starts from 5/1/2007 and ends on 4/30/2008