Issue with stacking accumulation in bar chart

[vishnus85]

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.

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,

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 !!

[Kushal_Chawda]

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

[vinieme12 · Accepted solution]

Try by adding Each Stack element as a separate expression

Meaning only keep one field in dimension

[jonathandienst]

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

[vvira1316]

Hi,

How about following

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;

[vishnus85]

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

[vishnus85]

Hi Vijay,

You are impossible !! This is too good and that is exactly the output that I am looking for. Thanks a lot for spending so much time to get me a solution. I will definitely adopt this if I don't see a better solution.

What I attached was a sample file. In actual scenario, its a bit more complex as the time dimension needs to be a cyclic one toggling between Month, Quarter and Year. And the metric itself is going to be sales per representative (where the representative count also have to be cumulative).

So I am still looking for an easier solution if possible in set analysis. 

Vishnu

[vishnus85]

See the reply from Vijay Vira below. That is the resultant graph I am looking for.

[vvira1316]

Hi Vishnu,

I'm glad to know that it may help you. All the best.

Best Regards,

Vijay

[kamielrajaram]

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