Grouping by Month and Summing Totals by Month

[Former Member]

     Hi All,

Here is my problem. I grouped my report by item. Then I made a second  group level  by date and chose to have the date grouped by months. Then I made a sum of the sales field and had the totals listed for each group level (in the image it is called TOTAL VALUE) , which in this case would mean the totals for every month per item.  Now, I need a field that shows every item's  % of total sales (all items) for each month. For example if my report shows me that "item1" 's total sales for Jan was 100.00 and for Feb 150.00 etc. I would have to divide that by the company's total sales for Jan and Feb etc. in order to show this item's sales % of total company sales. The problem is that I do not have anyway of putting the total sales summarized BY MONTH into a formula. If anyone can help I would really be happy!! Thanks to you All!!!

[abhilash_kumar]

Hi Manny,

Try this please:

1) Create a formula with this code (😞

If Month({date field}) = 1 then

{Sales field to sum};

2) Create a similar formula for each Month and just increment the number by 1 for each month. For e.g: Feb would be:

If Month({date field}) = 2 then

{Sales field};

3) The % formula would be:

If Month({date field}) = 1 then

Sum({sales field}, {date field used as group}, "monthly") % Sum({})

Else if Month({date field}) = 2 then

Sum({sales field}, {date field used as group}, "monthly") % Sum({@Feb})

Else if

.

.

.

.

Else if Month({date field}) = 12 then

Sum({sales field}, {date field used as group}, "monthly") % Sum({@Dec})

Hope this helps.

-Abhilash

[abhilash_kumar]

My apologies for that reference to a user. It was supposed to read

@ Jan

@ Feb

-Abhilash