cancel
Showing results for 
Search instead for 
Did you mean: 

Date Range (starting on Sunday) summary quantity amounts

geraldkuiper
Explorer
0 Kudos

I been ask to make a report here. I have a table that has our parts number, quantity, and date in it. This table is for shipping to our customers. I need to make a report that will summary the shipment quantity to our customers in that week.

For example

Part Number Customer Week 1 Week 2 Week 3 . . . . . Week 52

ABC Customer 1 333 22 32 90

XYX Customer AB 33 0 33 1000

The date is like this:

Part Number: ABC
Customer: Customer 1
Date: 02/27/2023
Quanity: 111
Part Number: ABC
Customer: Customer 1
Date: 02/28/2023
Quanity: 112
Customer: Customer 1
Date: 03/01/2023
Quanity: 110

I need to get formula to check to see if the date range is between Sunday - Saturday if so then add in the quantities to it. Then add in another formula field for week 2 check to the follow week and add in the quantities for that week. And repeat for another week.

Then on the report I need to replace Week 1, Week 2, Week 3 headers with the date that begins on Sunday for that week.

I have something in a cross-tab report but I had totals to it that I need to remove, but wasn't able to. I manage to remove the grand totals.

Any help in getting the formula working here would be greatly appreciated.

Thank you,
Gerald

Accepted Solutions (0)

Answers (3)

Answers (3)

raghavendra_hullur
Active Contributor
0 Kudos

Hi,

when you are referring the totals getting displayed, I think it's the sub totals rather than grand totals.

To suppress the sub totals for a column row combination, just click on row/column field values highlighted in red below and check the checkbox highlighted (in yellow below) for Suppress Subtotal option for whichever row/column field it's enabled.

Hope that helps.

Thanks,

Raghavendra

ido_millet
Active Contributor
0 Kudos

Right-click the CrossTab,
select the option of Cross-Tab Expert...
Select the Customize Style tab.
That dialog has options for suppressing Grand Totals:

ido_millet
Active Contributor
0 Kudos

Insert a CrossTab.

Se row field as Customer.

Set column field as Date but set the grouping option for that field as week.

Set the quantity column as the value to summarize.

geraldkuiper
Explorer
0 Kudos

The CrossTab will still have a row of total. My team doesn't want to see the total. I can't seem to supress the total rows