topic Re: Set Analysis Question in App Development

Set Analysis Question

Anonymous — Tue, 02 Oct 2018 18:17:45 GMT

Hi All,

I have a retail sales table with transaction number, sales, article code, and promotion name. Each transaction has multiple rows. Sales information and promotion information are stored in different rows.

Transaction No	Article Code	Sales	Promo Name
147	P101	20	-
147	G101	-	$5 off
148	P102	25	-
148	G103	-	Spend more get more

I'm trying to do a promotion 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. The formula I'm using right now is Sum( {1<[Transaction Key]=P({<[Promo Name]=[Promo Name]>})>}[Net Sales]). And the result I'm getting is like below - all the sales are grouped to the null value of promo.

Promo Name	Sales
$5 off	0
-	45

Ultimately, I would like to have a table of results like below, with the sales properly grouped by promotion name. Is this something able to be done in set analysis? Or I would have to change the data structure?

Promo Name	Sales
$5 off	20
Spend more get more	25

Thanks,

Shirley

Re: Set Analysis Question

sunny_talwar — Tue, 02 Oct 2018 18:25:28 GMT

When you select "$5 off", you continue to see "Spend more get more"?

Re: Set Analysis Question

Anonymous — Tue, 02 Oct 2018 18:33:06 GMT

Sorry my mistake, I have corrected the post. When selecting a specific promo, I only saw one promotion information in the table. However ultimately I would like to construct a table containing all the promotion information, with the sales properly grouped to each promotion name.

Re: Set Analysis Question

sunny_talwar — Tue, 02 Oct 2018 18:45:57 GMT

Try this

Only({1<[Transaction No] = P([Transaction No]), [Promo Name]>} Aggr(Sum(TOTAL <[Transaction No]> {1<[Transaction No] = P([Transaction No]), [Promo Name]>} [Sales]), [Transaction No], [Promo Name]))

No selection

With Selection

Re: Set Analysis Question

Anonymous — Tue, 02 Oct 2018 19:04:58 GMT

Thanks for the reply Sunny! Could you explain the logic of the formula?

Why did you use "Only" and "Total" here?

Re: Set Analysis Question

sunny_talwar — Tue, 02 Oct 2018 19:07:50 GMT

TOTAL is used to get the sum of sales by transaction no.

ONLY is used so that I can use the same set analysis outside of the Aggr() as it is used inside. Else the set analysis restrictions won't work

Re: Set Analysis Question

Anonymous — Tue, 02 Oct 2018 19:44:55 GMT

I don't think the formula works. I selected BabyEvent, and this is the result I got. Maybe it's due the fact that multiple discount could associate with one same transaction ID? But I dont't know why there is also no sales showing for BabyEvent.

Re: Set Analysis Question

sunny_talwar — Tue, 02 Oct 2018 19:54:32 GMT

I have no idea what to tell you based on a screenshot... may be if you are able to share your qvf or a sample... I might be able to check it out.

Best,

Sunny

Re: Set Analysis Question

Anonymous — Tue, 02 Oct 2018 20:21:42 GMT

Hello Sunny,

Thanks for all the help! I have shared some sample data. Hopefully it can give you a more clear view of the problem.

Shirley