Transactions

[dannyb29]

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

 

[Charliy]

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.

 

[dannyb29]

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

 

[SkipVought]

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!

 

[SkipVought]

Do you have a Promo Table?

Something like...
PromoID
ProductCode

 

[dannyb29]

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

 

[dannyb29]

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

 

[SkipVought]

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.

 

[SkipVought]

I really doubt that you ought to do this with if...then...else logic.

 

[pmax9999]

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