Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Transactions

Status
Not open for further replies.

dannyb29

Technical User
Nov 10, 2010
90
GB
Hi Guys, I hope you are well.

I am trying to create my own groups within Crystal based on a set of sales trasactions. My aim is to count the occurances my employee sells a promotion. For example:
If a customer buys 1 or more kit kat and 1 or more salt and vinegar crisps, i need to count that transaction and then call it KitKat Promotion.
If a customer purchases 1 or more mars and 1 or more twix then i need to count that transaction and call it Mars Promo. Both products have to be within the same transaction ID in order for it to count as a promotion

This is how i will need the report to look.

Kitkat Promo Mars Promo Malteeser Promo
Lisa Hansel 1 3 5
Dan Tring 0 8 1
Rich Staines 2 1 10

I hope this makes sense

Many Thanks

Dannyb
 
We'd need a better idea of what your data looks like.

If there are multiple records for each transaction, then you'll need to use shared variables.
 
Hi,
Yes there are multiple records for each transaction. Here is a data sample:

TransactionID: Product code: Product description: Qty: Staff ID
101 20500 KitKat 1 5021
101 20501 Crisps 2 5021

TransactionID: Product code: Product description: Qty: Staff ID
102 20502 Twix 1 9841
102 20503 Mars 2 9841



This would then show in my report


Kitkat Promo Mars Promo
Lisa Hansel 1 0
Dan Tring 0 1


Thanks
Danielle
 
How many promotions do you have?

Is that all the promos that you will ever have?

Is a promo always and only defined by the purchase of two items? For instance, what if the customer bought a kit kat, crisp, Mars & twix in one transaction?

Lots more questions of this kind!
 
Hi SkipVought

I am currently working with 6 different promotions, but as you rightly asked, this could increase as the months go by. Also yes I would like to count the transactions if there are more than two items as well and group that as a separate group so that would increase the number of promotions. At the moment there are only 4 products that i need to report on.

Thanks D
 
Hi Skip,
Thanks for you quick response.
There is no promo table, which is why i am trying to create my own promo groups manually. Painful process :(

thanks
D
 
A Promo Table will be the way to go. You might include the start & end dates for the promo, some metric(s) for evaluating the success or effectiveness of each promo. I'm already seeing that you may need several table to effectively hold and capture the data to evaluate current promos and report the effectiveness of historical promos, compare campaigns, design new more effective campaigns, etc.
 
OK, I achieved what I understand to be the required output this way:
[ul]
[li]Create 4 Running Totals (one for each Product) as follows:
[Pre]
Name: Ct: KitKat
Field to Summarize: Qty
Type of Summary: Sum
Evaluate: Use a formula: {Table.Product_Code} = 20501
Reset: On Change of Group (Staff_ID/Staff Name as required)
Repeat for each product, amending formula code to reflect correct Product Code[/Pre][/li]
[li]Place RTs in GF1a and Suppress section [/li]
[li]Create Formulas for KitKat Promo and Mars Promo as follows:
[Code {@KitKat_Promo}]
If Isnull({#Ct: KitKat}) or
Isnull({#Ct: Crisps})
Then 0
Else 1
[/Code]
[Code {@Mars_Promo}]
If Isnull({#Ct: Mars}) or
Isnull({#Ct: Twix})
Then 0
Else 1
[/Code]
[/li]
[li]Place the 2 formulas in GF1b [/li]
[li]Move Group Name to GF1b [/li]
[li]Suppress GH1 and Details Sections [/li]
[/ul]

Hope this helps

Cheers
Pete

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top