topic Re: Set Analysis Help in App Development

Set Analysis Help

Anonymous — Mon, 05 Nov 2018 22:56:26 GMT

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 No	Article Code	Sales	Promo Name
147	P101	$20	-
147	P101	-	$5 off
148	P101	$10	-
148	G101	$15	-
148	G101	-	$5 off
149	C123	$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 Name	Article Code	Promo Sales
$5 off	P101	$20
$5 off	G101	$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

Re: Set Analysis Help

sunny_talwar — Tue, 06 Nov 2018 01:04:24 GMT

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])

Re: Set Analysis Help

sunny_talwar — Tue, 06 Nov 2018 01:29:53 GMT

Sample attached

Re: Set Analysis Help

Anonymous — Fri, 09 Nov 2018 16:24:58 GMT

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.