Trial Balances and Financial Summaries | CLEARIFY

I would like to see these subjects formatted in an alternative manner... Have one Debit, Credit or Amount field and have one row per month.

Add a field to each row indicating period.  It would make the table much longer but way more easier for manipulation with pivot tables.... Could then add the date indicator as a "column" and use Slicers and Filters to select periods interested in. Could also add calendar table for grouping.

As it is now, you need to always add or take away the "periods" you want to look at...is frustrating for clients.  

Thanks

Fran

YES, YES, YES, YES!!

There is data stored in one of the company tables that comes from the config tool that should have this possible. 

Chuck, could we discuss this on a phone call soon. This would really help us :)  :)

Hey Brian,

Chuck has stated he will be doing some of these things in latger releases.  In the meantime, I actually use the GL Detail, filter it for the date I want (to bring in less data) and then I can format dates as needed,If I have to do something fancy footwork with dates.  That being said,I still use the side by side periods for reports and have figured out how to change the labels in excel so that the real period is shown.

Are you going to Sleeter?  

Fran

Financial Summaries  - and Financial Reporting in General - is one of the areas we are taking a closer look at; for a variety of reasons.

One of the pain points we hear - and which you have indirectly pointed out - is that amount columns have to be switched out if you want to choose a different period, as opposed to a filter which leaves the column in place.  Either way you have to manually choose an option, but leaving the column in place avoids possible Excel formatting issues - even though you can use the QQube clean up feature.

Let's look at the existing mechanism:  For a regular pivot table, our current method means that you don't have to create a calculated column if you want several periods side by side - something we know that a regular pivot table doesn't handle, because it is an 'if' statement.

To help solve this issue, having pre 'calculated' columns, e.g. this month, last month, ytd, last ytd, etc. would go a long way to just opening up a spreadsheet, and refreshing the data - without having to switch out a column.  This is something in the pipeline for the calendar dimensions - and the existing financial summaries.

Fran's idea is similar to all the detail subjects, where each transaction detail line has a date id - except that you are rolling up lines into a singular/granular date definition.  Something you can already do, for say a profit and loss in the general ledger (balance sheet would not be possible - until the next version where will have a mechanism in the general ledger detail to do exactly that). 

There are several questions/issues to deal with:

1. How do we present this as a new feature without end user confusion? (we obviously can't replace the old method, because of the legacy users out there)
2. What is the granular date definition? Period?  Week?  If it is week or even day(?) then we would add new extraction mechanisms - and add to the refresh time.
3. Does having the pre-calculated columns solve the issue?  to what degree?

We are still listening to your comments...

First, I am excited this is something you are looking into! thanks for taking a good look at it:

To address the issues you mentioned above:

1. How do we present this as a new feature without end user confusion? (we obviously can't replace the old method, because of the legacy users out there)

I agree. don't replace the old method. it definitely has its place. Perhaps you could add the new features as a new subject area

2. What is the granular date definition? Period?  Week?  If it is week or even day(?) then we would add new extraction mechanisms - and add to the refresh time.

At this point in time, I am fine with months as the periods, just as the financials summaries currently have. I can get the other periods using the GL Detail.

(by the way, I do use the GL Detail thru PowerPivot for a lot of my reports but 3 years worth of GL Detail for us has a huge file size and becomes a bit of a monster. I do like the financial summaries for quick financial summaries for 3 years)

3. Does having the pre-calculated columns solve the issue?  to what degree?

Well, most of our end users don't like to look at PivotTables (i know, i know, they need to get used to them. therefore, we "re-format" the reports into more static sheets and employ calculations (like "SumIFS", etc) on the table data to get what the users want to see. therefore, we do a lot of summing on different critera columns you have in the data. I don't think losing the pre-calcuated columns would hurt us in this specific instance becuase we can do the summing ourselves.

HOWEVER, and this is a BIG HOWEVER, having the pre-calculated columns does help tremendously in a pivottable!! Those of us who are comfortable with pivottable are VERY GRATEFUL to the have the pre-calcualted columns. we use them a lot.

I hope this makes sense

Fran, Thanks for replying. I won't be going sleeter but I need to go sometime :)

Chuck and Braden,

Hey Braden,

Sorry to hear you are not attending Sleeter Conf. It is well worth the time and effort.

I almost always use pivot tables cause its so easy to summarize. (And clients kick and scream).  So I tend to use lots of calculated columns to make them work and display correctly.  If customer needs custom groupings, I will ususally set up a template and fill in as you describe using SUMIFS or INDEx/Match or SUMProduct type functions. With my own calculated columns it is much easier to control PT appearance.

Chuck --- PreCalc columns is good to a point but most of the time I have the customer define what "Current" is and then work from there.  For financials, it is rarely the current period but usually current minus 1 or 2.  I then have a calculated column for "Currrent" and "YTD" or "LastYTD" all based on user defined date.   If I want month to month compares, I use QQube's trans. month name and trans year name for columns. And like him, I find this much easier using GLDetail. 

I agree with Braden, if I know the period I am looking at off the bat, the current financial periods are a great way to get quick peeks. But that is often not the case. 

So how about a GL Summary(vs financial summary) - tranactions by month showing month dates. A summarized GL Detail report.

Month is most freuent period. Use Weeks for other subjects, like Inventory.

If you are going to keep the periods with real names, please get the special coding out of the labels. Can't use in formulas or anything.

Fran

We have introduced period shortcuts in QQube Version 6.1

Dynamic column naming is not possible in a database, as the column names must have fixed names. However the Month names in the Excel Add-In have specific month or period dates/names e.g. an Alias to Period 1, 2, 3 because of the ability to store that within Excel, and make it easier to end users. 

For those who perform manual SQL or custom spreadsheet functions, the original period name will have to continue to suffice.

We will be looking at future versions of QQube with regard to aggregation of something other than month for financials only, and weigh the functionality vs the added overhead vs the usability.

I get the dilemma with Periods on Financial summaries.  Wouldn't a Period alias table work?  What I have run into  is that the "Period names" alias used in the pivots had some hidden code formatting in them so you could not look/search for the actual name. Will this be fixed? so we can search for the Alias, like March 2017? 

NOTE: this thread is for advanced users only.

We don't see this request as a 'fix', because nothing is broken.  For the average user, out of the box, they just drag the field, and off to the races.

For customization - and where manual SQL may come in, you are expressing a desire to have a direct correlation between period 1 and the month/year, such as we have programmed in the QQube Add-In

Period 31 is the actual name

July, 2017 is the alias that is in the header - it is a property known as "Custom Name" and is available programmatically once chosen,  The Custom Name comes from our QQube Add-In.

But obviously not available before being chosen, as again, it is already 'calculated' in the add-in.

In the database world, there is no practical application as a table that contains aliases of column names. Generally, aliases table and column names are allowed by some database vendors as part of the SQL.  It sounds good, but doesn't get you where you need to go in this case.

Normally we could create a semi-date dimension table with 6 columns to assist on the manual side of things for a custom developer: 

1. Column value from 0-36 so you can easily add and subtract columns to get a desired result - this is very much like using windowed functions in a database
2. Column value from 0-12 for each year 
3. Column year
4. Column month number (so you can have correct fiscal years).
5. Column month name
6. Column month end date

This may be akin to your request for an 'period alias' table, but this works properly when you have one amount column.  In our case, we have defined several hundred columns.  This presents a logistical problem - and would mean a different engineering process - and secondary/additional presentation for financial summary.   All we can say, is that is a consideration, much research needed, and with no promises.  

Since the real issue, is the naming of the column date, the normal tack is to create a list of aliases, and use an Excel array based formula to retrieve - something you probably are already doing.  We also use similar programming constructs to get the names in the Excel Add-In.  

We appreciate your persistence on this, and apprise you of any decisions we make in this area.