topic Re: QlikView Sum Up Number If Contain that Specific Item in QlikView

QlikView Sum Up Number If Contain that Specific Item

jerfworkS — Wed, 17 Nov 2021 07:24:42 GMT

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.

Re: QlikView Sum Up Number If Contain that Specific Item

MarcoWedel — Wed, 17 Nov 2021 17:25:50 GMT

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

hope this helps

Marco

Re: QlikView Sum Up Number If Contain that Specific Item

jerfworkS — Thu, 18 Nov 2021 01:48:31 GMT

Thanks MacroWedel!