Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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:
This expression should work for you:
Sum( {1<[Transaction No]=P({<[Promo Name]=[Promo Name]>})>} Sales)
Hello Petter,
Thanks for the reply! Seems like it worked! Could you explain the logic of this function?
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:
Thanks for the explaination!