Creating date range buckets for Sales Summary in QQube | CLEARIFY

I want my inventory sales by item summary to include columns of multiple date ranges to reveal sales trends, we use the following: Last 7 Days, Last 30 Days, Last 60 Days, Last 90 Days. 

We currently generate each of these reports in QB, export each and combine in a single Excel spreadsheet and use lookups to create the actual report.  Very time consuming.

In QQube, I understand how to use CalYr Txn Date to create a Report Filter for the entire report.  But I need those four date ranges in a single pivot table.  The A/R and A/P reports have Aging Buckets with date ranges.  Can something similar be done in the Inventory module?

Much thanks!

Daniel

Hi Daniel,

You are on right track...Using a QQube dynamic list..Add a 'calculated column' for your 'buckets'.  (It has to be right next to your dynamic list (no blk columns inbetween).  You would need to set up an anchor date from which to measure the 30-60-90 days.     I usually insert 1-2 rows at row 1(so my 'list' starts at row 3). This allows me to add a title, last update or or information to my list. I usually set my anchor dates somewhere in the first row.

Your formula in calculated column would be something like:  if(anchordate-txndate<=7,"Last 7 days",anchordate-txndate<=30,"Last 30 days",anchordate-txndate <=60,"Last 60 days","")

Then on you pivot, you make your "bucket" column a "column" and filter it so the ""(blanks) do not show...

Hop that helps,

Fran

Hello, Fran.

Again, thank you for the response!

I have created an Inventory List with the data I need.  I then added a calculated column that fills in the field with the text phrase, as noted above (only a slight change needed in the formula!).  Once the data all looked good, I converted the List to a Pivot Table and began recreating the layout.  The problem is, I don't see my custom column (simply labeled Aging) anywhere in the fields available and so I cannot add it to the Pivot Table.

Where is my custom column hiding?

For the heck of it I tried converting back to a List and my original List layout and data was completely gone.  So I guess that it was a mistake to take my data-ready List and convert it to a Pivot Table.  The wiki doesn't help with this.  What step am I missing?

Thank you, Fran!

Daniel

Daniel,

Glad to help... You may have to "change data source" under pivot table options to expand selection to cover new column. 

So, I have rebuilt my Inventory List with the data I need including the custom bucket column as we have been discussing.  I have saved a back-up of this file.  Upon clicking Convert to Pivot Table, my Inventory List is gone and I am presented with a blank Pivot Table.  Converting to a Table is not preserving anything I did in the List.  My custom bucket column is not available for use.  The "change data source" option under Pivot Tables only allows me to change external data source.  There must be something I am doing wrong.  The wiki describes how to create calculated columns in a List or in a Table, but doesn't mention being able to pull it from one to the other.

Help!

Fran is giving you good assistance here.

Even though QQube has the data, it requires some Excel manipulation to get this done.

What you need to do in Excel:

1. Use QQube to pull in your list data
2. Create a new column with a formula e.g. if Year = 2010, and Month = Feb then sales quantity, else 0
3. This new column becomes part of your 'database'
4. Using the native Excel functionality go to the Insert Tab and insert a pivot table

You should have your fields in a blank pivot table - including the ones you created with the formulas.  There are two keys here: 

• Add the calculated columns to the pivot table list as shown here: http://community.clearify.com/wiki/view/312/excel-lists-creating-dynamic-calculations
• Use the Excel native functionality (NOT the QQube Pivot Table Button) to convert the list to pivot table

You will have both a list on one sheet, and a pivot table on the other.

Please also remember that you can't filter out a pivot table using dates, without filtering out the inventory history.  In order to sum up the proper quantities, all transactions from day one are encompassed in the pivot table.  You need to create formulas, as outlined here, to put your transactions into buckets.