topic Re: Set analysis question in QlikView

Set analysis question

Wed, 06 Nov 2013 21:27:59 GMT

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

Re: Set analysis question

rustyfishbones — Wed, 06 Nov 2013 21:37:55 GMT

Hi,

I am not sure I understand the question, SUM(NetSales) with Department as Dimension should give you what you need

See attached

but maybe you have not explained it properly, or I have not understood it

Re: Set analysis question

Wed, 06 Nov 2013 21:45:54 GMT

Thanks for the reply Alan.

That doesn't work because it only sums the line items that are in that department. I want the total sales of the entire transaction for every transaction that has at least 1 item in the department.

For department A I'm looking for a value of 217 (everything in transaction 1 and 4 because both of those have an item in department A) while using Sum(NetSales) will only give me 93 (54 + 39).

I hope this clarifies my question.

Regards

Re: Set analysis question

Wed, 06 Nov 2013 21:50:52 GMT

Try something like this:

Sum(aggr(sum(NetSales), TransactionKey))

with Department as dimension

Re: Set analysis question

Wed, 06 Nov 2013 21:55:45 GMT

Hi Claudio,

I appreciate the feedback but that doesn't produce the correct results. I get this with your formula:

Department	Net Sales
	517
A	217
B	133
C	0
D	167
E	0
F	0

Thanks,

Dan

Re: Set analysis question

rustyfishbones — Wed, 06 Nov 2013 22:01:18 GMT

Hi Dan,

How do you expect to get Department A NetSales 217?

Are the NetSales not Summing to 93 based upon your example

Re: Set analysis question

Wed, 06 Nov 2013 22:03:00 GMT

OK, i you should group by Department too,

sum(aggr(sum(NetSales),TransactionKey,Department))

Re: Set analysis question

Wed, 06 Nov 2013 22:08:48 GMT

Alan,

I get 217 by adding up all 6 lines that belong to transaction 1 and 4. What I need to do is find the total transaction net sales for every transaction that has at least 1 item in a given department. So in department A's case there are two transactions that have an item in department A and those are transactions 1 and 4. Therefore I want the total net sales for transaction 1 and 4 which is 217.

I don't want the 93 result because that only includes 2 lines (the one department A line from transaction 1 and the one department A line from transaction 4). I need all 6 lines from the two transactions.

Thanks,

Dan

Re: Set analysis question

Wed, 06 Nov 2013 22:10:50 GMT

Thanks again Claudio but that still didn't do the trick. I get these results:

Department	Net Sales
	517
A	93
B	201
C	102
D	32
E	4
F	85

Re: Set analysis question

rustyfishbones — Wed, 06 Nov 2013 22:10:59 GMT

that will give the same result as SUM(NetSales)

Re: Set analysis question

qliksus — Wed, 06 Nov 2013 22:31:49 GMT

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

Re: Set analysis question

Wed, 06 Nov 2013 22:37:42 GMT

Qliksus,

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

Best regards,

Dan

Re: Set analysis question

Fri, 08 Nov 2013 10:09:29 GMT

Hi,

Thanks for the post,

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

Thanks in Advance.

Regards,

Pavan Kumar.

Re: Set analysis question

qliksus — Fri, 08 Nov 2013 12:29:24 GMT

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

Re: Set analysis question

Sun, 10 Nov 2013 08:36:21 GMT

Hi Susant thanks for your clear explanation.