Skip to main content
Applications are open for the 2024 Qlik Luminary Program. Apply by December 15 here.
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis Help

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. Promotion information and sales information are stored in different rows.

Transaction NoArticle CodeSalesPromo Name




147P101-$5 off
148G101-$5 off

* Please be noted that the article P101 is on promo for transaction 147 but not for transaction 148.

I'm trying to do some sales analysis for specific promotions. For example, when I select "$5 off", it will return the article sales on promotion, so $20 (P101 from transaction 147) adding $15 (G101 from transaction 148) - $35 in total. The desired result is illustrated below.

Promotion NameArticle CodePromo  Sales
$5 offP101$20
$5 offG101$15

The formula I came up with is like this:

sum({<[Transaction No]=p(), [Article Code]=p(), [Promo Name]= >} [Sales])

It works in most situation, but not in the example I gave - P101 is on promo for transaction 147, but not for transaction 148. With my formula it will return all the possible values of Transaction No and Article Code associated with the selected Promo Name, resulting in a total sales value of $45 instead of $35.

Any suggestions on tackling this problem will be greatly appreciated!



1 Solution

Accepted Solutions
3 Replies

Have you considered creating a new field which combines Transaction No and Article Code into one field like this...

LOAD [Transaction No],

     [Article Code],


     [Promo Name],

     [Transaction No] & '-' & [Article Code] as TransactionArticleKey

FROM ....;

and then use this

Sum({<[TransactionArticleKey] = p([TransactionArticleKey]), [Promo Name]>} [Sales])


Sample attached


Not applicable

Hi, thanks for the reply! I was hoping to solve the issue via set analysis, but seems like this is the only solution. I have adopted your suggestion and got the correct calculation.