Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
When you select "$5 off", you continue to see "Spend more get more"?
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.
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
Thanks for the reply Sunny! Could you explain the logic of the formula?
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]))
Why did you use "Only" and "Total" here?
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
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.
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
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