Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
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 ?

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

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

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

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:
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:
,
[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,

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

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