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

[techvarun]

You have to use ValueList function to achieve the result.

From QlikHelp
http://help.qlik.com/en-US/sense/June2017/Subsystems/Hub/Content/ChartFunctions/SyntheticDimensionFu...

[techvarun]

Please refer the attached

[Anonymous]

Hi Varun,

Could you give me an example with Sample data. I tried to understand from the above mentioned link. But bit confused.

Regards,

Krishna

[techvarun]

Sample qvw posted below. Please check

[Anonymous]

Hi Varun,

Certainly, value list helped to an extent. Is there any possibility that, while sum we can count quantity of other dimension.

For simple data model i directly updated quantity in the morning. but in my application i need to sum/ count the quantity based on other dimension.

Thanks to confirm.

[Anonymous]

Hi Varun,

For better understanding i attached excel sheet with sample table and info as comments. Thanks to advice. !

[Kushal_Chawda]

How would you define line name A,T,B & C also NONE, in your sample data I don't see it

[Anonymous]

Hi Kushal,

Condition as below:

If(Line Name like"A S*", A,If(Line Name like "NONE" and Familyname "TINT", 'T', if(Line Name = "B S*", B, IF(LINE Name like 'C S*', C'))))

Finally Line Name NONE and Family Name <> TINT then it is NONE. Which i dont want to see in char sales.

[Kushal_Chawda]

see the attached