Aggregation - one parameter responds to selection, one ignores

[Anonymous]

Hi All,

I have a retail sales table with transaction number, sales, article code, and promotion name. Each transaction can have multiple articles, and some might be on promotion, some might not.

Transaction No	Article Code	Sales	Promo Name
147	P101	20	$5 off
147	G101	10	-
148	P102	25	Spend more get more
148	G103	5	-

I'm trying to do a basket analysis for specific promotions. For example, when I select "$5 off", all the transactions that contain this promotion will be selected, in this case Transaction No.147. And all the sales of this transaction - on promotion and not, will be summed up, so $30. I also need the article information  in order to do a distribution analysis. So the result I want is like below after selecting the promo "$5 off"

Transaction No	Article Code	Promo Basket Sales
147	P101	20
147	G101	10

My approach for this was trying to do an aggregation with set analysis, making the sales summing part to ignore the selection. And then aggregate by Transaction No, which responds to the selection of promotion, and then by Article code, which ignores the selection of promotion.

My formula is like this:

sum(aggr({<[Promo Name]=>}Sum(Sales),[Transaction No], Concat({<[Promo Name]=>}[Article Code]&' ')))

But it didn't work. Any suggestions on tackling this problem?

Thanks

[petter]

This expression should work for you:

   Sum( {1<[Transaction No]=P({<[Promo Name]=[Promo Name]>})>} Sales)

[Anonymous]

Hello Petter,

Thanks for the reply! Seems like it worked! Could you explain the logic of this function?

[petter · Accepted solution]

The element function named P() gives you the Possible values from the set expression that is inside the P() and make them the selected values on the left-hand-side of the assignment (=) operator in the outer set expression.

So it finds the promo names that are the selected promo names

Sum( {1<[Transaction No]=P({<[Promo Name]=[Promo Name]>})>} Sales)

and it does an associative selection (Qlik calls this an implicit field value definition) from this onto the transaction numbers.

Sum( {1<[Transaction No]=P( {<[Promo Name]=[Promo Name]>} )>} Sales)

There are more examples here:

https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/ChartFunctions/SetAnalysis/set-mod...

[Anonymous]

Thanks for the explaination!