9 Replies Latest reply: Feb 11, 2015 12:23 PM by Michael Solomovich RSS

    Current Year and Prior Year Gross Amount Calculation

    Shan Ahmed

      Hi,

      I have Gross amount calculation different for pror to 2015 and in 2015. The filtering should be based on invoice year.

       

      The Calculation i have in 2015 is:
      =num(num(Sum({<SalesType = {'SalesRegister', 'FreeText', 'Service'}>}LineAmount),'##,##0')-(

      num(Sum({<GLAccount={101100,115100,115110,115120,225430,420020},SalesType={'FreeText'}>}LineAmount),'##,##0') + num(Sum({<ItemId={'GW_CLX','GW_XTS','CR_L','CR_U'}, SalesType={'SalesRegister'}>}LineAmount),'##,##0')) ,'##,##0')

       

      And Prior to 2015 is:
      =num(num(Sum({<SalesType = {'SalesRegister', 'FreeText', 'Service'}>}LineAmount),'##,##0')-

      num(Sum({<GLAccount={101100,115100,115110,115120,225430,420020},SalesType={'FreeText'}>}LineAmount),'##,##0'),'##,##0')

       


      How to i put the Invoice year in these two piece of expression that will show me the gross amount Prior to 2015 and In 2015 only?

      I appreciate your help.

       

      Thanks,

      Shan

        • Re: Current Year and Prior Year Gross Amount Calculation
          Michael Solomovich

          If I understand you correctly, this should work:

           

          =num(
          Sum({<SalesType = {'SalesRegister', 'FreeText', 'Service'}>}LineAmount)
          -
          (
          Sum({<GLAccount={101100,115100,115110,115120,225430,420020},SalesType={'FreeText'}>}LineAmount)
          +
          if(Year=2015,Sum({<ItemId={'GW_CLX','GW_XTS','CR_L','CR_U'}, SalesType={'SalesRegister'}>}LineAmount),0)
          )
          ,'##,##0')

           

          I made the part with ItemId conditional depending on Year.  Also removed unnecessary num() functions for easier read.

          Please count my open/close parenthesis, it is easy to mis-type...

            • Re: Current Year and Prior Year Gross Amount Calculation
              Shan Ahmed

              Michael,

               

              Thanks for your reply. The Current year apply for everything in this calculation. So, In that case i apply the 'If' logic for the whole expression right?

               

               

              Also,

              How can i calculate the gross amount the prior to 2015 year?

               

              Appreciate your help!

               

              Thanks,

              Shan

                • Re: Current Year and Prior Year Gross Amount Calculation
                  Shan Ahmed

                  is this expression i am trying is incorrect?

                   

                   

                  =

                   

                  num(num(Sum({<SalesType = {'SalesRegister', 'FreeText', 'Service'},[Invoice Year]={$(=Year(Today()))}>}LineAmount),'##,##0')-(

                  num(Sum({<GLAccount={101100,115100,115110,115120,225430,420020},SalesType={'FreeText'}>}LineAmount),'##,##0') + num(Sum({<ItemId={'GW_CLX','GW_XTS','CR_L','CR_U'}, SalesType={'SalesRegister'}, [Invoice Year]={$(=Year(Today()))}>} LineAmount),'##,##0')) ,'##,##0')

                  Thanks,

                  Shan

                    • Re: Current Year and Prior Year Gross Amount Calculation
                      Michael Solomovich

                      Technically it looks correct.  Logically - I can't tell, it depends on what you need.  Can only tell what it is doing:

                       

                      Of the three parts, the 1st and the 3rd are calculated only for the current invoice year, and the 2nd for all years.  If it is what you need, it is correct.

                        • Re: Current Year and Prior Year Gross Amount Calculation
                          Shan Ahmed

                          Michael,

                           

                          I was trying with your expression and i am getting zero values for this expression. I think i am doing wrong here.

                           

                           

                          =

                           

                          num(
                          if([Invoice Year]<=2015,Sum({<SalesType = {'SalesRegister', 'FreeText', 'Service'}>}LineAmount),0)
                          -
                          (
                          if([Invoice Year]<=2015,Sum({<GLAccount={101100,115100,115110,115120,225430,420020},SalesType={'FreeText'}>}LineAmount),0)
                          +
                          if([Invoice Year]<=2015,Sum({<ItemId={'GW_CLX','GW_XTS','CR_L','CR_U'}, SalesType={'SalesRegister'}>}LineAmount),0)
                          )
                          ,'##,##0')

                           

                           

                          Thanks,

                          Shan

                            • Re: Current Year and Prior Year Gross Amount Calculation
                              Michael Solomovich

                              Well, it doesn't look like my expression.  It is logically your first expression for 2015 but with condition:

                               

                              if("Invoice Year"<=2015, <your expression>, 0)

                              Given that today all years are <=2015, it should work as your expression.

                              Can you upload a small example of your application, so I (or anyone else) can take a look?

                                • Re: Current Year and Prior Year Gross Amount Calculation
                                  Shan Ahmed

                                  Michael,

                                   

                                  Sorry if i misinterpret it incorrectly. I didnt mean its your expression. I was trying to say that i was trying to use yours and came up with the mentioned expression.

                                   

                                  Thanks,

                                  Shan

                                  • Re: Current Year and Prior Year Gross Amount Calculation
                                    Shan Ahmed

                                    Hi Michael,

                                     

                                    I just uploaded a piece of my allication. Could you please have a look at it and suggest/Advise how i can achieve what i want in the application?


                                    The Gross amount Calculation is different in year 2015 than prior to year 2015.

                                    So when i want to calculate gross amount in 2015 i just want only the year 2015 gross amount. But when i mean prior to 2015 then all the year prior to 2015 gross amount should populate. And I would be able to filter based on year and by customer as well.

                                    The way i am trying to calculate is not working the way i want it to be.

                                     

                                    Highly appreciate your help!

                                     

                                    Thanks,

                                    Shan

                                      • Re: Current Year and Prior Year Gross Amount Calculation
                                        Michael Solomovich

                                        Hi Shan,

                                         

                                        I got time to take a look.

                                         

                                        The 2015 expression is incorrect because of the wrong field names - it should be [Sales Type] instead of SalesType, and ItemID instead of ItemId.  The "prior to 2015" looks fine, and works as you described.

                                         

                                        I do have a question about the logic though.  It maybe correct or not...  How the record should be counted if it fits in more than one part of the expression?  In the other way, the expression:

                                        sum(<set1> Amount) + sum(<set2> Amount)

                                        may returns different result then

                                        sum((<set1>+<set2>) Amount)

                                        In the first case, if a record is included in both set1 and set2, it will be counted twice.  In the second case, it will be counted only once.  If no any record can be in both sets, than it doesn't matter what approach to use.

                                         

                                        Regards,

                                         

                                        Michael