Set analysis question

[#1]

Hi Everyone,

Thank you for taking a look at my question.

Here's the scenario:  I have a list of transactions and each transaction is made up of multiple line items that each belong to one department and have a net sales associated.  The data source is below:

TransactionKey	Department	NetSales
1	A	54
1	B	90
1	E	4
2	B	35
2	C	98
3	D	32
3	B	76
3	F	59
4	A	39
4	C	4
4	F	26

What I'm trying to do is create a straight table with Department as the dimension and have an expression that sums the entire transaction net sales for each transactions that has an item sold from the department.  The results would look like this:

Department	Net Sales
A	217
B	448
C	202
D	167
E	148
F	236

For example, department A has net sales of 217 because it TransactionKey 1 and 4 have an items sold from department A so I summed both of those transactions.

I've made several attempts to get the expression working by trying to aggregate Net Sales by TransactionKey but I'm not able to get it working properly.  If anyone could provide an example of the correct syntax I would greatly appreciate it!

Thank you,

Dan

[qliksus · Accepted solution]

Hi,

Please use the expression as

sum(Aggr(Sum(Total <TransKey> NetSale),Dept,TransKey))

For reference

Dept	sum(Aggr(Sum(Total <TransKey> NetSale),Dept,TransKey))
A	217
B	448
C	202
D	167
E	148
F	236

[#3]

Qliksus,

That is a work of art I am truly impressed!  Thank you very much for your help!

Best regards,

Dan

[#4]

Hi,

Thanks for the post,

Can you please explain the expression. Its very tricky for me to understand.

Thanks in Advance.

Regards,

Pavan Kumar.

[qliksus]

Hi Pavan,

If you break the requirement into small pieces then

part1: summation of total netsales by Transkey

part2: show the summation against respective dimension (Dept) which is a part of the Transkkey

part 1-OP: sum(Total <Transkey> NetSale)

Part2-OP: If u use above expression directly it will give total sales across the chart , not the total respective to selective Transkey.

You need AGGR function, it can be

Aggr(Sum(NetSale),Dept,TransKey)  : You need to put all dimension  which relates to your             requirement   part1   and part2

If you sum part-1OP & part-2OP then

Sum(Aggr(Sum(Total <TransKey> NetSale),Dept,TransKey))

Suggest you to do some exercise to get better undestanding.

Sorry if i don't able to expalin u how u want.

Regards,

Susant

[#6]

Hi Susant thanks for your clear explanation.