cancel
Showing results for 
Search instead for 
Did you mean: 

Summing up QTY on Hand

Former Member
0 Kudos

I am developing a report where I have qty on hand. I am putting it into a report that reports sales and need to show qty on hand for all locations. Example. I have stores numbered 1,2,3,4,5 but only stores 1 and 2 have sales and the report shows that they sold 3 each for a total of six size 9 shoes sold. However, the qty on hand field needs to show the qty on hand in all stores. So, if each store has 5 size 9 shoes left, this field should 25 size 9 shoes left.

I am sure I need to write a formula to pull all stores inventory into some sort of variable, then total it and put it on the report, but I can't get my brain around it. Any help would be appreciated.

Accepted Solutions (1)

Accepted Solutions (1)

former_member292966
Active Contributor
0 Kudos

Hi Edison,

If your report has two tables linked like:

Location -> Sales

You can change the link type to Left Outer Join. This will return records for each of your locations and the sales for the matching locations.

So even if a location has no sales, it should appear. No other major changes to the report or logic should be required.

Good luck,

Brian

Former Member
0 Kudos

This didn't seem to help. So, I just started over. But, my summing of quantity on hand continues. I have gotten the report to produce the correct quantities on hand if I run the report for 1 day. But, I need to see sales on this report. So, now, everytime I add a day, it multiplies the on hand count by the number of days. So, if the report is for yesterday, then the count for an item is 17; which is correct. But, if I run the report for the past two days, the on hand jumps to 34. I have no clue how to solve this.

former_member260594
Active Contributor
0 Kudos

Edison,

This may be a bit of a hack resolution but what if you divided the onhand quantity by the date difference in days? for example;

{Qty.OnHand}/ datediff('s', minimum(), maximum())

former_member292966
Active Contributor
0 Kudos

Hi Edison,

I see the problem now. The quantity is returned with each record and multiplies for each record for each location.

Graham's suggestion is definitely the best. Since the quantity on hand is a constant and you only want one instance of it use the Max function or the Min function.

The Max is unique to the report or group and will not change unless it changes between groups.

Good luck,

Brian

Former Member
0 Kudos

I don't really understand. Max condition of what? What I have learned so far is that it is really on incrementing on days it had items sold and what the inventory was on that day...so it seems. This is veery confusing.

former_member292966
Active Contributor
0 Kudos

Hi Edison,

I'm going to make some huge assumptions here because I don't know how your report is laid out.

Assuming your report is grouped like:

Group1 - Location

Group2 - SalesDate

Your report is laid out like:

                    QTY On Hand          Sold 
  Location 1 
    June 1              2                  0
    June 2              1                  1
    June 3              0                  1
  Total                 0                  2

                    QTY On Hand          Sold 
  Location 2 
    June 1              5                  0
    June 2              5                  0
    June 3              5                  0
  Total                 5                  0

                    QTY On Hand          Sold 
  Grand Total           5                  2

To get the total for QTY on Hand try this formula:

Assuming you have the totals in GroupFooter1, drop the {table.QTYONHAND} field onto the GroupFooter1 section. This will show the quantity on hand from the last record.

To get the grand total for the QTY on hand, create a formula like,


WhilePrintingRecords; 
NumberVar QTYonHand;

QTYonHand := QTYonHand + {table.QTYONHAND}; 

Drop this formula onto the GroupFooter1 section also. This formula has a variable called QTYonHand and will accumulate each time the formula is called in GroupFooter1.

Now create one last formula like:

WhilePrintingRecords; 
NumberVar QTYonHand;

Drop this into the Report Footer where the Grand Total should be. This will be be the total for the QTYonHand.

Good luck,

Brian

Former Member
0 Kudos

I appreciate that, and I thought we were close there. the added complication is the items inidvidually. So, here is what it needs to look like

Sales Report for days 05.30.09 through 06.02.09

Barcode Description Vendor Size Color Qty sold Qty currently on hand

1234 Super Shirt Bob L Blue 10 100

1235 Supershirt Bob M Blue 8 150

So, this tells me I have sold 10 L Blue Super Shirts during those 4 days and have 100 left.

My Report has the following groups

Vendor Name

Barcode

Store Number

Date of Sale

Thanks for your help if you can interpret this. I am lost in the weeds now.

Former Member
0 Kudos

Is there a way to sum the quantity based on today's numbers only?

Answers (0)