13 Replies Latest reply: Sep 19, 2017 12:20 PM by Vijay Vira RSS

    Issue with stacking accumulation in bar chart

    Vishnu Sreekumar

      Hi Friends,

       

      I am trying to build a stacked bar chart where the expression is a cumulative sum of sales. The x axis is time in months and the stack dimension is category. So I want the stack dimension to accumulate over time and show up in the following months (even if a month in consideration did not have any value for a particular category).

       

      Now this accumulation works correctly if I am not adding category dimension as displayed below.

      Without Category.PNG

      But the moment I add category as a second dimension (to stack), the chart skews up. If a particular month doesn't have value for a particular category, that category is excluded from the bar for that month. See the below diagram when Category dimension is added to the same chart. The month FEB-16 should show a cumulative total of 15 for me with the categories rolled up till then. But,

      With Category.PNG

      The expression I am using is RangeSum(Above(Sum([Sales Qnty]),0,RowNo()))

      I have also tried Just Sum([Sales Qnty] with "Full Accumulation" option checked - but that isn't helping either.



      Is there any way I can handle this tweaking the expression or so ?



      I am attaching the sample qvw and data source. Please help !!

        • Re: Issue with stacking accumulation in bar chart
          kushal chawda

          How actually your stack should look like if you add category?

          • Re: Issue with stacking accumulation in bar chart
            Vineeth Pujari

            Try by adding Each Stack element as a separate expression

            Meaning only keep one field in dimension

            • Re: Issue with stacking accumulation in bar chart
              Jonathan Dienst

              You could use the full accumulation option with the simple Sum([Sales Qnty]) expression.

               

              Capture.PNG

               

              Capture2.PNG

                • Re: Issue with stacking accumulation in bar chart
                  Vishnu Sreekumar

                  Thanks Jonathan,

                   

                  As you can see, for Aug-15 you have some value for category 'Medication' which is not carried over to Oct-15. So this works fine if there are some valid sales for all 5 categories for all months. But if there is no sales for a category in one month, the cumulative sales of that category until that month is missing from the bar. The chart that I am looking for is the way Vijay Vira's output is.

                   

                  Vishnu

                • Re: Issue with stacking accumulation in bar chart
                  Vijay Vira

                  Hi,

                   

                  How about following

                   

                   

                  CatgoricalAccumulationinStackChart.PNG

                   

                   

                   

                   

                   

                  SET ThousandSep=',';
                  SET DecimalSep='.';
                  SET MoneyThousandSep=',';
                  SET MoneyDecimalSep='.';
                  SET MoneyFormat='$#,##0.00;($#,##0.00)';
                  SET TimeFormat='h:mm:ss TT';
                  SET DateFormat='M/D/YYYY';
                  SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
                  SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
                  SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

                  NoConcatenate
                  MonthYearTmp:
                  LOAD Distinct Date#(Capitalize(Left(MonthYear, 3)) & '-20' & Right(MonthYear, 2), 'MMM-YYYY') as AMY_MonthYear
                  FROM
                  [..\Data\Test11.xlsx]
                  (
                  ooxml, embedded labels, table is Sheet1);

                  NoConcatenate
                  ProceduralDataTmp:
                  LOAD Date#(Capitalize(Left(MonthYear, 3)) & '-20' & Right(MonthYear, 2), 'MMM-YYYY') as MonthYear
                  ,
                  [Sales Qnty] as [Procedural Sales Qnty]
                  FROM
                  [..\Data\Test11.xlsx]
                  (
                  ooxml, embedded labels, table is Sheet1)
                  Where Category = 'Procedural';

                  NoConcatenate
                  MedicationDataTmp:
                  LOAD Date#(Capitalize(Left(MonthYear, 3)) & '-20' & Right(MonthYear, 2), 'MMM-YYYY') as MonthYear
                  ,
                  [Sales Qnty] as [Medication Sales Qnty]
                  FROM
                  [..\Data\Test11.xlsx]
                  (
                  ooxml, embedded labels, table is Sheet1)
                  Where Category = 'Medication';

                  NoConcatenate
                  OtherDataTmp:
                  LOAD Date#(Capitalize(Left(MonthYear, 3)) & '-20' & Right(MonthYear, 2), 'MMM-YYYY') as MonthYear
                  ,
                  [Sales Qnty] as [Other Sales Qnty]
                  FROM
                  [..\Data\Test11.xlsx]
                  (
                  ooxml, embedded labels, table is Sheet1)
                  Where Category = 'Other';

                  NoConcatenate
                  InclusionDataTmp:
                  LOAD Date#(Capitalize(Left(MonthYear, 3)) & '-20' & Right(MonthYear, 2), 'MMM-YYYY') as MonthYear
                  ,
                  [Sales Qnty] as [Inclusion Sales Qnty]
                  FROM
                  [..\Data\Test11.xlsx]
                  (
                  ooxml, embedded labels, table is Sheet1)
                  Where Category = 'Inclusion';

                  NoConcatenate
                  ExclusionDataTmp:
                  LOAD Date#(Capitalize(Left(MonthYear, 3)) & '-20' & Right(MonthYear, 2), 'MMM-YYYY') as MonthYear
                  ,
                  [Sales Qnty] as [Exclusion Sales Qnty]
                  FROM
                  [..\Data\Test11.xlsx]
                  (
                  ooxml, embedded labels, table is Sheet1)
                  Where Category = 'Exclusion';

                  NoConcatenate
                  CrossDataTable: 
                  LOAD AMY_MonthYear as  MonthYear
                  // ,LookUp('Procedural Sales Qnty', 'MonthYear', AMY_MonthYear, 'ProceduralDataTmp') As [Procedural Sales Qnty] 
                  ,If(IsNull(LookUp('Procedural Sales Qnty', 'MonthYear', AMY_MonthYear, 'ProceduralDataTmp')),0,LookUp('Procedural Sales Qnty', 'MonthYear', AMY_MonthYear, 'ProceduralDataTmp')) As [Procedural Sales Qnty] 
                  ,
                  If(IsNull(LookUp('Medication Sales Qnty', 'MonthYear', AMY_MonthYear, 'MedicationDataTmp')),0,LookUp('Medication Sales Qnty', 'MonthYear', AMY_MonthYear, 'MedicationDataTmp')) As [Medication Sales Qnty] 
                  ,
                  If(IsNull(LookUp('Other Sales Qnty', 'MonthYear', AMY_MonthYear, 'OtherDataTmp')),0,LookUp('Other Sales Qnty', 'MonthYear', AMY_MonthYear, 'OtherDataTmp')) As [Other Sales Qnty] 
                  ,
                  If(IsNull(LookUp('Inclusion Sales Qnty', 'MonthYear', AMY_MonthYear, 'InclusionDataTmp')),0,LookUp('Inclusion Sales Qnty', 'MonthYear', AMY_MonthYear, 'InclusionDataTmp')) As [Inclusion Sales Qnty] 
                  ,
                  If(IsNull(LookUp('Exclusion Sales Qnty', 'MonthYear', AMY_MonthYear, 'ExclusionDataTmp')),0,LookUp('Exclusion Sales Qnty', 'MonthYear', AMY_MonthYear, 'ExclusionDataTmp')) As [Exclusion Sales Qnty] 
                  Resident MonthYearTmp;

                  NoConcatenate
                  StraightDataTable:
                  LOAD MonthYear
                  ,
                  [Procedural Sales Qnty] as [Sales Qnty]
                  ,'Procedural'
                  as Category
                  Resident CrossDataTable;
                  Concatenate
                  LOAD MonthYear
                  ,
                  [Medication Sales Qnty] as [Sales Qnty]
                  ,'Medication'
                  as Category
                  Resident CrossDataTable;
                  Concatenate
                  LOAD MonthYear
                  ,
                  [Other Sales Qnty] as [Sales Qnty]
                  ,'Other'
                  as Category
                  Resident CrossDataTable;
                  Concatenate
                  LOAD MonthYear
                  ,
                  [Inclusion Sales Qnty] as [Sales Qnty]
                  ,'Inclusion'
                  as Category
                  Resident CrossDataTable;
                  Concatenate
                  LOAD MonthYear
                  ,
                  [Exclusion Sales Qnty] as [Sales Qnty]
                  ,'Exclusion'
                  as Category
                  Resident CrossDataTable;

                  DROP Table ProceduralDataTmp;
                  DROP Table MedicationDataTmp;
                  DROP Table OtherDataTmp;
                  DROP Table InclusionDataTmp;
                  DROP Table ExclusionDataTmp;
                  DROP Table MonthYearTmp;
                  DROP Table CrossDataTable;

                  • Re: Issue with stacking accumulation in bar chart
                    Kamiel Rajaram

                    Good Day,

                    Hope this helps. Changes were made to the script and to the Chart (Expression). Unticked Suppress Zero-Values on the presentation tab.

                     

                    Kind Regards

                    Kamiel

                    • Re: Issue with stacking accumulation in bar chart
                      Vishnu Sreekumar

                      Hi All,

                       

                      Thanks a lot for the different options you all provided. In my actual scenario I found that the best solution was to

                       

                      - remove category dimension

                      - month-year will be the only dimension

                      - setting the chart styling to 'stacked'

                      - added 5 expressions (one each for each category)

                      - expression example  RangeSum(Above(Sum{<Category={'Inclusion'}>}([Sales Qnty]),0,RowNo()))

                       

                      Cumulative Stack Solution.png

                       

                      Regards,

                      Vishnu

                      • Re: Issue with stacking accumulation in bar chart
                        Sunny Talwar

                        Not sure, but can this work?

                         

                        Capture.PNG

                         

                        Script

                         

                        Table:

                        LOAD MonthYear&Category as Temp,

                        TimeSort,

                            MonthYear,

                            Category,

                            [Sales Qnty]

                        FROM

                        [Cumulative Stack Example.xls]

                        (biff, embedded labels, table is Sheet1$);

                         

                        TempTable:

                        LOAD MonthYear

                        Resident Table;

                         

                        Join (TempTable)

                        LOAD Category

                        Resident Table;

                         

                        Concatenate(Table)

                        LOAD MonthYear,

                        Category

                        Resident TempTable

                        Where Not Exists(Temp, MonthYear&Category);

                         

                        DROP Table TempTable;

                         

                        Expression

                        Aggr(RangeSum(Above(Sum([Sales Qnty]),0,RowNo())), Category, MonthYear)

                         

                        or this if you don't have MonthYear sorted correctly in script and you have QV12 or above

                        Aggr(RangeSum(Above(Sum([Sales Qnty]),0,RowNo())), Category, (MonthYear, (numreic)))

                        • Re: Issue with stacking accumulation in bar chart
                          Vijay Vira

                          Hi Vishnu,

                           

                          I'm glad to know that you found the solution you were looking for. If you won't mind and if possible can you please post what exactly worked for you (expressions, settings, etc.) with sample data/app for the help of community.

                           

                          Thanks,

                          Vijay