4 Replies Latest reply: Dec 9, 2017 11:09 AM by Sunny Talwar RSS

    Using Aggr() and Dual

    André Ficken

      Hi There,

       

      I would like to create the following horizontal stacked bar chart over this year and the 4 years prior:

      per year, show a horizontal stacked bar that consists of 3 categories:

      category3: the total revenue generated by customers where the revenue >= 0 and <20k

      category2: the total revenue generated by customers where the revenue >=20k and <50k

      categorie1: the total revenue generated by customers where the revenue >=50k

       

      The second horizontal stacked bar chart that I would like to create over the same period as the previous chart

      is per year in the same categories the number of unique customers have have been generating the revenue per category.

       

      I have been trying quite a few things, but I have not hit the jackpot yet...

      My dimensions are:

      - the 5 years requested

      - the 3 categories, using the Aggr() and Dual#

       

      I am having difficulties with grouping the years and the categories and to get the count and sum of the expressions to match with the categories. I am using set analysis a lot since the dashboard has a static year/previous year and from/to date settings.

      So my set analysis starts with {1<  and not {$<

       

      I need some simple guidelines to get the aggr and dual right and the sum and count in the expressions.

       

        • Re: Using Aggr() and Dual
          André Ficken

          Additional information: I have been able to get my stacked bar graph working for 1 year (2017):

          My dimensions are: [Selection Year] eg. 1 year only. and the categories <=20000, 20000-50000 and >50000 by using aggr and dual:

           

          =Aggr(

          if(Sum( {$< [Record_Type] = {Invoice'},

                      [CompanyType] -= {'*Internal*'},

                      [ItemType] -= {'Travel','Materials','*Intern*'},

                      [Year] = {2017}

                    >}[Amount]) <= 20000, Dual('0-20k',1),

          if(Sum( {$< [Record_Type] = {'Invoice'},

                      [CompanyType] -= {'*Internal*'},

                      [ItemType] -= {'Travel','Materials','*Intern*'},

                      [Year] = {2017}

                    >}[Amount]) <=50000, Dual('20-50k',2),

          if(Sum( {$< [Record_Type] = {'Factuur'},

                      [CompanyType] -= {'*Internal*'},

                      [ItemType] -= {'Travel','Materials','*Intern*'},

                      [Year] = {2017}

                    >}[Amount]) >50000, Dual('50+k',3))))         

          ,  CustCode

           

          My Expression:

           

          Sum( {$< Record_Type={'Invoice'}, [CompanyType] -= {'*Internal*'},

                     [ItemType] -= {'Travel','Materials','*Intern*'},

                     [Year] = {2017}         

            >} DISTINCT CustCode)

           

          This displays correct info on the count of customers as well as sum of total invoice amount.

          What I now need is to expand this graph from 1 selection  year to 5 years (Selection Year - 4)

          So that I get 5 stacked bars with 3 categories. Hope you can get me some advise how to get this done.

            • Re: Using Aggr() and Dual
              Sunny Talwar

              Did you try to add Year to the Aggr() function and see if that works

               

              =Aggr(

              if(Sum( {$< [Record_Type] = {Invoice'},

                          [CompanyType] -= {'*Internal*'},

                          [ItemType] -= {'Travel','Materials','*Intern*'},

                          [Year] = {">=2013<=2017"}

                        >}[Amount]) <= 20000, Dual('0-20k',1),

              if(Sum( {$< [Record_Type] = {'Invoice'},

                          [CompanyType] -= {'*Internal*'},

                          [ItemType] -= {'Travel','Materials','*Intern*'},

                          [Year] = {">=2013<=2017"}

                        >}[Amount]) <=50000, Dual('20-50k',2),

              if(Sum( {$< [Record_Type] = {'Factuur'},

                          [CompanyType] -= {'*Internal*'},

                          [ItemType] -= {'Travel','Materials','*Intern*'},

                          [Year] = {">=2013<=2017"}

                        >}[Amount]) >50000, Dual('50+k',3))))       

              ,  CustCode, Year)

               

              Expression

               

              Sum( {$< Record_Type={'Invoice'}, [CompanyType] -= {'*Internal*'},

                         [ItemType] -= {'Travel','Materials','*Intern*'},

                         [Year] = {">=2013<=2017"}        

                >} DISTINCT CustCode)