Merging different results into one based on different conditions

[Anonymous]

Hi All,

I have data as shown in below. Could some one suggest me to build stack barchart,  how to get Derive Line Name  and show sum() as shown in result set.

First three columns available in script. How to derive line name as Dimension? Sum of result set shown in last.

Line Name	Family Name	Quantity	Derive Line Name
A Sales	Z	100	A
A Sales	O	90	A
A Sales	S	70	A
A Sales	BF	70	A
A Sales	E	60	A
A Sales OS	E	40	A
A Sales OS	S	70	A
A Sales OS	Z	70	A
A Sales OS	BF	60	A
A Stock Sales	Z	90	A
A Stock Sales	S	70	A
A Stock Sales OS	S	70	A
NONE	O	60	exclude in Chart
NONE	TREATMT	40	exclude in Chart
NONE	GLAZING	100	exclude in Chart
NONE	TINT	90	T
NONE	COAT	70	exclude in Chart
B Sales	Z	70	B
B Sales	BF	60	B
B Sales	S	40	B
B Sales	E	70	B
B Sales OS	BF	60	B
C Sales	BF	40	C
C Sales	S	100	C
C Sales	Z	90	C
C Sales	E	70	C
C Sales OS	S	40	C
C Sales OS	BF	70	C
C Stock Sales	Z	70	C
C Stock Sales	S	60	C
C Stock Sales OS	S	90	C

Result set with expression:

Derived Line Name	Quanity
A	sum(A) - Sum(T)
T	Sum(T)
B	Sum(B)
C	Sum(C)

Please suggest me, is it possible to do with out touching script and just with Dimensions and Expressions?

Thanks to suggest.

Regards,

Krishna

[Anonymous]

Hi Kushal,

In the sample application quantity is part of same dimension. but in my application quantity is coming from facts table and line name , family name are part of product table. Line code is key column between two tables. In this case i am unable to group line name for A,B,C and for line name T it is combination of T and family name. Please suggest me considering quantity is in another table. Even in my sample data i mentioned both as different tables.

Thankyou.

[Kushal_Chawda]

Can you join both the tables? If you can join both the table on DateKey and Line Code ,you can follow my approach

[Anonymous]

Not possible Kushal. We can not join Facts and dimensions.

[Kushal_Chawda]

Any specific reason?

[Anonymous]

There are many other dimensions connected to facts and Management does not accept to merge. Strictly they oppose script level changes.