Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vishnus85
Partner - Creator
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.

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

1 Solution

Accepted Solutions
vinieme12
Champion III
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.

View solution in original post

13 Replies
Kushal_Chawda

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

vinieme12
Champion III
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.
jonathandienst
Partner - Champion III
Partner - Champion III

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

Capture.PNG

Capture2.PNG

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

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;

vishnus85
Partner - Creator
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

vishnus85
Partner - Creator
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

vishnus85
Partner - Creator
Partner - Creator
Author

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

vvira1316
Specialist II
Specialist II

Hi Vishnu,

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

Best Regards,

Vijay

kamielrajaram
Creator III
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