4 Replies Latest reply: Mar 18, 2016 9:51 AM by PAUL YEO RSS

    why count expression need to use AddYears ?

    PAUL YEO

      Hi All

       

      Table 1 expression return correct value on count no of company :-

       

       

      Count(distinct{$<year = {$(=Max(year)-0)}, date = {"<=$(=Date(AddYears(Max({<year={$(=Max(year))}, sales = {'*'}>} date), -0)))"}, month>}company)

       

       

      Table 2 expression return correct value on compute sales :-

       

       

      Sum({$<year = {$(=Max(year)-0)}, month = {"<=$(=Max({<year={$(=Max(year))}, sales = {'*'}>} month))"}>}sales/$(Columndim89)/1000)

       

       

      Does any one know why table 1 count expression need to use AddYears ?

       

       

       

       

      Paul

        • Re: why count expression need to use AddYears ?
          Sunny Talwar

          For table 1, the expression which is enabled is the second one, which is this:

          =count({$<year = {$(=max(year))},month = {"<=$(=max({<year={$(=max(year))}>} month))"}, company= {'=sum({$<year = {$(=max(year))}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} sales)>1'}>} distinct company)

           

          When I enable the AddYear expression, I get a count of 9. Not sure which one is correct.

            • Re: why count expression need to use AddYears ?
              PAUL YEO

              Hi Sunny

               

              Below both expression work :-

               

              count({$<year = {$(=max(year))},month = {"<=$(=max({<year={$(=max(year))}>} month))"}, company= {'=sum({$<year = {$(=max(year))}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} sales)>1'}>} distinct company)

               

              Count(distinct{$<year = {$(=Max(year)-0)}, date = {"<=$(=Date(AddYears(Max({<year={$(=Max(year))}, sales = {'*'}>} date), -0)))"}, month>}company)

               

              But when i see you did count by invoice , the expression you share with me is below :-

               

              Count(distinct{$<year = {$(=Max(year)-0)}, date = {"<=$(=Date(AddYears(Max({<year={$(=Max(year))}, sales = {'*'}>} date), -0)))"}, month>}inv)

               

              So i conclude that above first expression is not require. As it is very complicated.

               

              Then i just wonder why Count by coy or invoice need to use AddYears ?

               

              Paul