Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Shirley
Contributor II
Contributor II

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

147

P101

$20

-
147P101-$5 off
148P101$10-
148G101$15-
148G101-$5 off
149C123$5-

* 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!

Thanks,

Shirley

1 Solution

Accepted Solutions
sunny_talwar

3 Replies
sunny_talwar

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

LOAD [Transaction No],

     [Article Code],

     [Sales],

     [Promo Name],

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

FROM ....;


and then use this

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

sunny_talwar

Sample attached

Capture.JPG

Shirley
Contributor II
Contributor II
Author

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.