topic Re: Issue with stacking accumulation in bar chart in QlikView

Issue with stacking accumulation in bar chart

vishnus85 — Fri, 15 Sep 2017 01:04:37 GMT

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

Re: Issue with stacking accumulation in bar chart

Kushal_Chawda — Fri, 15 Sep 2017 07:51:58 GMT

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

Re: Issue with stacking accumulation in bar chart

vinieme12 — Fri, 15 Sep 2017 07:57:38 GMT

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

jonathandienst — Fri, 15 Sep 2017 08:09:16 GMT

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

Re: Issue with stacking accumulation in bar chart

vvira1316 — Fri, 15 Sep 2017 17:23:40 GMT

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;

Re: Issue with stacking accumulation in bar chart

vishnus85 — Fri, 15 Sep 2017 18:52:51 GMT

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

vishnus85 — Fri, 15 Sep 2017 19:07:21 GMT

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

Re: Issue with stacking accumulation in bar chart

vishnus85 — Fri, 15 Sep 2017 19:08:31 GMT

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

Re: Issue with stacking accumulation in bar chart

vvira1316 — Fri, 15 Sep 2017 20:27:29 GMT

Hi Vishnu,

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

Best Regards,

Vijay

Re: Issue with stacking accumulation in bar chart

kamielrajaram — Fri, 15 Sep 2017 21:53:24 GMT

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

vishnus85 — Tue, 19 Sep 2017 15:05:21 GMT

Thanks Kamiel,

I can see that this works in the example. But when I apply this in my actual scenario, it is not working for unknown reasons. I ended up adding five separate expressions (each for each category) in the chart to make it stack.

Regards,

Vishnu

Re: Issue with stacking accumulation in bar chart

vishnus85 — Tue, 19 Sep 2017 15:10:55 GMT

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()))

Regards,

Vishnu

Re: Issue with stacking accumulation in bar chart

sunny_talwar — Tue, 19 Sep 2017 15:14:35 GMT

Not sure, but can this work?

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

vvira1316 — Tue, 19 Sep 2017 16:20:49 GMT

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