6 Replies Latest reply: Jul 12, 2011 5:10 AM by Stefan Wühl RSS

    Set Analysis  - Dates

    Rajesh Vaswani

      Hi,

          I have the below expression. This expression returns me zero. Not sure where I am wrong.

       

      =Count({<[link_claim_Claim_Status]={"*"} - {"CLO"},[Start Date] ={"<=addmonths($(varPreYearFullDateEnd),1)"}>} DISTINCT [ClaimID])

       

      $(varPreYearFullDateEnd) has been assigned =Date('31/12/2010','DD/MM/YYYY'). I want to do the calculation for 31/01/2011 by adding 1 month.

       

      On the other hand.

       

      =Count({<[link_claim_Claim_Status]={"*"} - {"CLO"},[Start Date] ={"<=$(varPreYearFullDateEnd)"}>} DISTINCT [ClaimID]) just works fine.

       

      returns me the correct value. I just want to add 1 month. I tried many ways where I will have date in the number format, use sum etc..finally asking the community to help.

      Thanks in advance

      Rajesh Vaswani

        • Set Analysis  - Dates
          Stefan Wühl

          Hi Rajesh Vaswani,

           

          you need a dollar sign expansion also for the addmonths function to call it within the set expression:

           

          =Count({<[link_claim_Claim_Status]={"*"} - {"CLO"},[Start Date] ={"<=$(=addmonths($(varPreYearFullDateEnd),1))"}>} DISTINCT [ClaimID])

           

          Regards,

          Stefan

            • Re: Set Analysis  - Dates
              Rajesh Vaswani

              Hi Stefan,

                    Thankyou for your reply. After $ expansion too it does not work. I am trying to figure out why. Let me know if you could suggest anything?

              Thanks,

              Rajesh Vaswani

                • Re: Set Analysis  - Dates
                  Nagaian Krishnamoorthy

                  The following expression seems to work

                   

                  =Count({<[link_claim_Claim_Status]={"*"}-{"CLO"},[StartDate]={"<=$(varPreYearFullDateEnd+1)"}>} DISTINCT [ClaimID])

                  • Re: Set Analysis  - Dates
                    Stefan Wühl

                    Hi Rajesh Vaswani,

                     

                    I noticed two issues:

                     

                    with variable definition like

                    =Date('31/12/2010','DD/MM/YYYY')

                     

                    I needed to remove the $() around the variable name when using the variable as argument to AddMonths function, i.e.

                     

                    {<StartDate=  {"<=$(=AddMonths(varPreYearFullDateEnd,1))"} >}

                     

                    (I also struggle from time to time with variable evaluation).

                     

                    It is also important, that there is no space between the smaller than (<=) and the dollar sign of the dollar sign expansion (I get zero as result otherwise).

                     

                    As Erika wrote, I think a MakeDate would be clearer to understand what you want to achieve (assigning a date to a variable), so your expression should work as well.

                     

                    In my test app, it works quite well, if you have any more problems, it may be useful if you could post a small sample application.

                     

                    Regards,

                    Stefan

                • Re: Set Analysis  - Dates
                  Deepak Vadithala

                  Hi Rajesh,

                   

                  Stefan is right and using dollar sign expansion should do the trick. Although with these kind of requirement I generally prefer to load the master calendar table in the load script. I had attached an example of master calendar for your reference. If you look into the master calendar you will see I have MTD, WTD, YTD & previousYear etc. Also, if I am re-using same expressions within other objects then I would declare my expression as variables and use the dollar sign expansion. This approach helps me to maintin the calculations in one centralised location (Variable Overiew Window). And the re-usability & maintainence of these expression is easy. This is just like adding an abstraction layer.

                   

                  I hope this helps!

                   

                  Cheers - DV

                  • Set Analysis  - Dates

                    Accrding to me Variable should be assigned as:

                     

                    varPreYearFullDateEnd=  assign it : =makedate('2010','12','31')