topic Re: How to accumulate values when having multiple dimensions? in QlikView

How to accumulate values when having multiple dimensions?

bill_mtc — Mon, 18 Apr 2016 03:37:39 GMT

Hi Community,

I would like to seek help from you if there are possibilities to accumulate values when having multiple dimensions in a straight table? This is a client requirement which I pretty need to address the concern. Find below the sample chart, I highlighted the column where it does not worked, even using Full Accumulation.

Attached herewith is the sample application used in the provided chart above.

NOTE: Seems like the accumulative will only work in having one dimension. Hope there is workaround for multiple dimensions.

Regards,

Bill

Re: How to accumulate values when having multiple dimensions?

ecolomer — Mon, 18 Apr 2016 04:04:40 GMT

See this:

Cumulative sum with two dimension.

Re: How to accumulate values when having multiple dimensions?

tamilarasu — Mon, 18 Apr 2016 04:32:03 GMT

Hi,

Try expression as

Rangesum(Above(TOTAL Sum(Sales),0,RowNo(TOTAL)))/Sum(TOTAL Sales)

Sort expression for product dimension as

=Aggr(Rank(TOTAL Sum(Sales)),Product,Brand)

Re: How to accumulate values when having multiple dimensions?

bill_mtc — Mon, 18 Apr 2016 07:32:47 GMT

Hi Tamil,

Thanks for the help. Would also like to ask how can I sort the report by Sales %? I modified the data a little, there are product with same brand but different supplier. But what should be displayed in the report is sorted by Sales % per Brand, regardless of the supplier.

What is currently happening is that it is sorted per Brand, per Supplier.

I cannot able to attach the updated sample application with updated inline data. Find below the updated inline data.

Data_tmp1:

Load * Inline [

Product, Brand, Supplier, Sales

A, AB, ABC, 10

B, AB, ABC, 50

C, CB, CDE, 32

D, DB, ABC, 60

E, DB, DEF, 88,

A, DB, HJI, 28

];

Re: How to accumulate values when having multiple dimensions?

swuehl — Mon, 18 Apr 2016 07:43:29 GMT

Like this?

Product	Brand	Sales	Sales %	Cumulative Sales
		268	100%	0%
A	AB	10	4%	4%
B	AB	50	19%	22%
A	DB	28	10%	33%
E	DB	88	33%	66%
D	DB	60	22%	88%
C	CB	32	12%	100%

Then just go to sort tab and promote the Brand column to the top.

edit:

Missed that you want to sort by Sum(Sales):

Product	Brand	Sales	Sales %	Cumulative Sales
		268	100%	0%
A	DB	28	10%	10%
E	DB	88	33%	43%
D	DB	60	22%	66%
A	AB	10	4%	69%
B	AB	50	19%	88%
C	CB	32	12%	100%

Re: How to accumulate values when having multiple dimensions?

bill_mtc — Mon, 18 Apr 2016 08:02:30 GMT

Hi swuehl‌,

It should be sorted by Sales % of Product, the brand must be disregarded. So the result would be descending order of Sales %. How would it possible?

Re: How to accumulate values when having multiple dimensions?

swuehl — Mon, 18 Apr 2016 08:06:28 GMT

Do you want Sales % shown in descending order or do you want sales % of product? That's a difference in your chart.

edit:

Maybe like this?

Product	Brand	Sales	Sales %	Cumulative Sales
		268	100%	0%
E	DB	88	33%	33%
D	DB	60	22%	55%
B	AB	50	19%	74%
C	CB	32	12%	86%
A	DB	28	10%	96%
A	AB	10	4%	100%

Re: How to accumulate values when having multiple dimensions?

tamilarasu — Mon, 18 Apr 2016 08:13:24 GMT

Hello Bill,

Create a calculated dimension as =Aggr(Rank(TOTAL Sum(Sales)),Product,Brand) and name it as "Rank" or something. Now, go to the sort tab and promote the Rank dimension to top. Also, make sure the Sort by should be Numeric values and Ascending order is selected. I have attached sample file for reference.

Re: How to accumulate values when having multiple dimensions?

bill_mtc — Thu, 28 Apr 2016 17:09:38 GMT

tamilarasu‌ this is amazing! Thank you for sharing. It works in my application.