Is running total the best solution?
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17165
Printed Date: 06 May 2024 at 1:08pm
Topic: Is running total the best solution?
Posted By: Robotacha
Subject: Is running total the best solution?
Date Posted: 31 Jul 2012 at 2:54am
|
Hello,
Im stuck!! hope someone can help.
This is what i need to achieve;
We have customers who purchase 2 different products (product A & product B).
We are running a points promotion on these 2 products for July / Aug / Sept/
If a customer purchases Product A, they get 1 point. if they purchase product B, they get 2 points.
Thats easy enough.
Simply;
GROUP 1 = CUSTOMER /JULY TOTAL /JULY PRD A /JULY PRD B/POINTS A/POINTS B/TOTAL POINTS
Lets say the customers July total was 95 units, with 35 units being prdt A and 60 units being prdt B.
his points would be 35+120 = total points 155.
The hard part;
Management want points accumulated ONLY on unit sales ABOVE the customer average monthly sales from Jan > Jun.
So if the customers average was 50 units per month for the first 6 months, then he would only accumulate points on 45 units. (July's 95 units - avg 50 units)
My problem now is, how do I know how many points to allocate to the 45 units over the average threshold?
I need to identify, exactly WHEN they exceed the 50 units average, then look at the remaining 45 unit purchases, and give 1 or 2 points based on whether its a product A or a product B. I have tried;
GROUP 1 = CUSTOMER /JULY TOTAL /JULY PRD A /JULY PRD B
Then in DETAILS, have the INVOICE DATE, with the UNIT Quantity, and a Running Total.
I get/understand this bit, and I can see when the RT exceeds 50 units.
GH1 = CUSTOMER A / JULY=95 / PRD A=35 / PRD B=60
Details = 01/07/2012 PRD A 5 unit RT = 5
03/07/2012 PRD B 7 unit RT = 12
05/07/2012 PRD A 10 unit RT = 22
09/07/2012 PRD B 4 unit RT = 26
12/07/2012 PRD B 4 unit RT = 30
12/07/2012 PRD B 19 unit RT = 49
14/07/2012 PRD B 1 unit RT = 50 <<< HERE
14/07/2012 PRD B 15 unit RT = 65
17/07/2012 PRD A 10 unit RT = 75
18/07/2012 PRD A 10 unit RT = 85
22/07/2012 PRD B 6 unit RT = 91
24/07/2012 PRD B 4 unit RT = 95
I then wrote another formula like; (which I was hoping to sum by customer and *2) IF {#rtotal0}>= 50 AND {@prductCHECK}="B" THEN {TRANSACTIONS.QTY}
BUT, I cannot then summarize this formula by customer as I am using a summarized field {RT} in the formula.
:|
Can anyone provide some assistance? or an easier method to achieve what i want to achieve?
Thanks for your help.
Mike.
 ------------- Regards, Michael Jones |
Replies:
Posted By: lockwelle
Date Posted: 31 Jul 2012 at 3:21am
|
well...the simple answer is don't summarize using an aggregate function. Use shared variables / running totals and create your own summaries. Yes more work, but it is the way around these situations. HTH
|
Posted By: Robotacha
Date Posted: 31 Jul 2012 at 3:31am
|
hello,
Yes, it helps slightly. I thought thats the route I would need to go down, but honestly, Im a right novice with shared variables, so im sort of in the same situation. I dont know where to start or even know what to 'share' what field / value would I declare as a shared variable? Can you sum shared variables?
Do you know of any threads i could look at for reference? ------------- Regards, Michael Jones |
Posted By: Robotacha
Date Posted: 31 Jul 2012 at 3:32am
|
What would you do in my situation? ------------- Regards, Michael Jones |
Posted By: lockwelle
Date Posted: 31 Jul 2012 at 4:13am
|
there are lots of threads with shared variables...i've written quite a few. here's the summary: shared variables typically come in groups of 3, increment, display, reset. reset, typically in a group header shared numbervar x; x := 0; "" // will hide the zero on your report display, typically in a group footer shared numbervar x; x increment, typically in the detail section shared numbervar x; if {table.field} = something then x := x + {table.field1} else x := x + {table.field2}; "" //again hides the value of x on the report = is used to test for values being equal := is used to assign a value to a variable. the increment uses an 'if' as i am assuming that you want to control what values are used. The if part can be as simple or as complex as you want and doesn't need the else...it's all up to you and how/what the report requirements are. HTH...some more
|
Posted By: Robotacha
Date Posted: 31 Jul 2012 at 4:19am
|
Yes, it certainly does help... some more :D
i will give it a go now and report my results soon ! thankyou very much for your help. ------------- Regards, Michael Jones |
Posted By: Robotacha
Date Posted: 31 Jul 2012 at 5:39am
|
I really dont know what Im doing.
I think im going to give up on this one :(
Cant seem to get the results I want. Cant work out the logic for it :(
This is what I have and what I want to achieve: http://img152.imageshack.us/img152/5163/93035810.jpg - http://img152.imageshack.us/img152/5163/93035810.jpg ------------- Regards, Michael Jones |
Posted By: lockwelle
Date Posted: 31 Jul 2012 at 6:51am
|
so why not a formula to display the running total, like: if runningTotal < 50 then 0 else runningTotal - 50 i don't use running totals, so I am not sure of the syntax, but that would be the idea.
|
Posted By: Robotacha
Date Posted: 01 Aug 2012 at 3:29am
|
I tried that, but it dotn work. ------------- Regards, Michael Jones |
Posted By: DBlank
Date Posted: 01 Aug 2012 at 4:22am
|
this is not my area of expertise at all but try running two values in the same shared variable. one is for your actual sum to determine the threshold (x), the other is your dispaly value (y)
group header set value
shared numbervar x;
shared numbervar y; x := 0; y := 0; summary formula for details calculation
shared numbervar x;
shared numbervar y; x := x + {TRANSACTIONS.QTY};
y := y + (if x >50 then {TRANSACTIONS.QTY} else 0);
display formula for footer
shared numbervar y;
|
Posted By: Robotacha
Date Posted: 01 Aug 2012 at 8:24pm
|
I will certainly try that when I get to work. Like yourself, Shared variables are not my area at all.
I'm normally very good with the logic side of things, but this one got me :( I will report my findings later. If all fails, I will simply produce a longer report with the details section and suppress any customers that have not achieved the 50 threshold. Then the person the report is for will have to sum up the points manually. Alot of my work / reports is purely to automate monotonous weekly / monthly tasks, but we can't please all the time eh ? Lol Thanks for everyone's help so far. ------------- Regards, Michael Jones |
Posted By: Robotacha
Date Posted: 06 Aug 2012 at 12:38am
|
PHEW! this was certainly a head test for me.
I have solved the problem. Thanks to DBlanks suggestion.
The main issue here, was I was trying to display everything in the group header.
ie;
GH1 - Customer - July units - UHP units - NON UHP units - Points accumulated.
This wasnt going to happen as i was using the shared variable formulas.
I trnasferred all this data into the group FOOTER, hid the details and GH1 sections, and the sums all add up nicely now. exactly what I wanted.
This report can now be put to bed!
thanks for all your help!
much appreciated. ------------- Regards, Michael Jones |