Using calculated fields in Pivot Table data grouped by date

[glasswalk3r]

Greetings,

I have a Pivot table that has the following fields:

creation_date
sales_channel

The creation_date is a date field as dd-mm-yyyy hh:mm:ss. I want create a pivot table that shows me the average sells made by a sales channel in a month.

To do that, I grouped the creation_date by month and counted the sales_channel values. After that, I thought by using the formula EOMONTH() to check how many day I have per month (even putting this "artificial" field in the source data) and using a calculated field in the pivot to make count(sales_channel)/day(eomonth(<month of="" creation_date="">;0)).

I'm not being able to make it work like this. The result I got is always zero when I expect to get something like 718/30.

Is it possible to do that?

Thank you</month>

 

[Fazza]

hi,

Instead of adding a calculated field to the pivot table, add it via SQL in between the source data and the pivot table. Such as with a new field that is the average daily sales for the month. Then sum this field in the pivot table.

The SQL might be like,

SELECT sales_channel, creation_date, sales_channel/Day(DateSerial(Year(creation_date),Month(creation_date)+1,0))
FROM your_data

To set up the pivot table this way, save the data file, go to a new file, ALT-D-P, choose the external data option & follow the wizard. From memory, Excel files, choose the file, OK, etc. If you get a message about no visible tables ('cause you didn't use a normal - as opposed to dynamic - named range then acknowledge the message then choose options & show system tables, which will let you choose the worksheet as the data source), continue to the end and then take the option to edit in MS Query. Then the 'SQL' icon & edit the SQL to add the new field. 'Open door' icon to exit MS Query & complete the pivot table. If you want the resultant worksheet can be moved into the original data file. Maybe google for examples either from me in old posts or elsewhere by others.

HTH