Announcements
cancel
Showing results for
Did you mean:
Partner - Creator

Issue with stacking accumulation in bar chart

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 ?

1 Solution

Accepted Solutions
Champion III

Try by adding Each Stack element as a separate expression

Meaning only keep one field in dimension

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
13 Replies

Champion III

Try by adding Each Stack element as a separate expression

Meaning only keep one field in dimension

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Specialist II

Hi,

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:
// ,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:
,
[Procedural Sales Qnty] as [Sales Qnty]
,'Procedural'
as Category
Resident CrossDataTable;
Concatenate
,
[Medication Sales Qnty] as [Sales Qnty]
,'Medication'
as Category
Resident CrossDataTable;
Concatenate
,
[Other Sales Qnty] as [Sales Qnty]
,'Other'
as Category
Resident CrossDataTable;
Concatenate
,
[Inclusion Sales Qnty] as [Sales Qnty]
,'Inclusion'
as Category
Resident CrossDataTable;
Concatenate
,
[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;

Partner - Creator
Author

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

Partner - Creator
Author

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

Partner - Creator
Author

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

Specialist II

Hi Vishnu,

Best Regards,

Vijay

Creator III

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

Community Browser