Solved

Crystal Crosstab formula

Posted on 2012-12-26
4
409 Views
Last Modified: 2012-12-26
I have a formula field that qualifies each transaction(record) with or without VS.
I would like to have a crosstab that has two columns that shows the average of each category by customer. Then a column that displays the difference.

Is this doable?    The cross tab is attached.  

Thanks!
0
Comment
Question by:evend
  • 2
4 Comments
 
LVL 14

Expert Comment

by:LinInDenver
ID: 38721434
I don't see an attachment, but this should be possible. I would create 2 formulas, one for records containing 'VS' and those that don't.

//Counts if they have VS in them
IF {table.field} like '*VS*' then 1 else 0


//Counts if they don't have VS in them
IF {table.field} like '*VS*' then 0 else 1

These two formulas would show a count of how many records contain VS. If you have an actual numeric field value you want represented, replace the 1s above with {table.field} containing the number you are after.

Base your cross tab on these two formulas.

To find the different, you would need to look into calculated members. These are fields you can add into cross tabs, but can be a little tricky.
0
 

Author Comment

by:evend
ID: 38721668
I have this formula to qualify  each transaction\record.  Do you have another means to establish the difference.  I want to get the difference between the average daily sales.
I can get the average daily sales by category using groups and summaries.  I just can not seem to do the math between groups?  

If {Vend_Visits.visit_DateTime} > {non_vending_equipment.in_service_date} Then
   "With VendScreen"
Else
   "Without VendScreen"

Here was the attachment.
Sample-Crosstab--.pdf
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 38722268
What differences are you trying to calculate?

If it is between WIth and Without Vend Screen use this formula

If {Vend_Visits.visit_DateTime} > {non_vending_equipment.in_service_date} Then
   {AmountField}
Else
   -{AmountField}

Reverse the - if you want the other difference

mlmcc
0
 

Author Closing Comment

by:evend
ID: 38722271
Thanks!  Worked.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now