QlikView Sum Up Number If Contain that Specific Item

[jerfworkS]

Hi experts, I have a question regarding a expression or set analysis. Example, I want to sum up the whole sales number if the whole transaction contain item 'ItemFree' 

Dataset:

TransactionID	Item	Sales
1	ItemA	100
1	ItemB	100
1	ItemFree	0
2	Item	200
2	Item	100

3	ItemA	200
3	ItemB	300
3	ItemFree	0

Expected result: 

TransactionID	Sales
1	200
3	500

In the expected result, we should not show TransactionID '2' because there is no item 'ItemFree' in the transaction.

I understand this can be done easily using backend script and change the data schema, but I just want to know if this can be done using expression or set analysis.

[MarcoWedel · Accepted solution]

Sum({$<TransactionID=P({$<Item={'ItemFree'}>})>} Sales)

see also: https://help.qlik.com/en-US/qlikview/May2021/Subsystems/Client/Content/QV_QlikView/ChartFunctions/Se...

hope this helps

Marco

[jerfworkS]

Thanks MacroWedel!