# App Development

Announcements
cancel
Showing results for
Did you mean:
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
MVP

Sample attached

3 Replies
MVP

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

[Article Code],

[Sales],

[Promo Name],

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

FROM ....;

and then use this

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

MVP

Sample attached

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.

Tags