12 Replies Latest reply: Nov 14, 2011 3:16 PM by swuehl RSS

Set analysis syntax error ?

Swarup_Malli

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

    The parentheses aren't balanced.

    • Re: Set analysis syntax error ?
      Swarup_Malli

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

      • Re: Set analysis syntax error ?
        m woolf

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

        • Re: Set analysis syntax error ?
          Swarup_Malli

          changed it ,still the same prob persists

          • Re: Set analysis syntax error ?
            m woolf

            Still unbalanced parentheses. Take out right parentheses after +1

          • Re: Set analysis syntax error ?
            swuehl

            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)

             

             

            • Re: Set analysis syntax error ?
              Swarup_Malli

              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

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

                • Re: Set analysis syntax error ?
                  Swarup_Malli

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

                  • Set analysis syntax error ?
                    swuehl

                    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

                      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

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