Viewing P&L data by transaction date in QQube | CLEARIFY

I have been evaluating Qqube and I realize that an attempt was made to simply the data by aggregating all the transactions by month and inserting 36 months of measures.  However, I find measures like "Period 34" to be a non-intuitive way to think about my data and if I attempt to build calculations off of this field I would have to update the calculation every month when we move from period 34 to period 35.  

Instead I would like to create my own measures using the transaction date to aggregate Week to date, Month to date, Quarter to date and year to date P&L statements.  I would also like to specify an "as of date" using the transaction date calendar and run the report for these fields from there.  Then for every period, as long as I update my "as of date" I don't have to change the measures in my P&L statement.

Can I currently do this PowerPivot using Qqube, and if not, can you show me how to access the daily transactions for the P&L so I can create the view I desire?

The premise of the financial summary, is that it is pre-aggregated.  Aside from the practical use, there are two technical reasons for this:  balance sheet needs prior-year pandl rolled up into retained earnings, and lessening the amount of data to deal with.

We will add more column options in our new release later this year in response to other suggestions similar to yours.

NOTE: (3/02/2020)   QQube has a Profit and Loss Detail Data Model

i am trying to build exactly this, but the link provided is outdated. Can I have a sample that will help me to understand GL detail subject and how I can build a really dynamic reports.

Thank you

Rus

Sorry for the broken link - it appears to have bee moved.  

There are several ways to approach your financial data:  Financial Summary or General Ledger Detail. 

You only need the G/L Detail if you need transaction/item level detail for filtering purposes - or doing actual, budget, and forecast in the same report - or doing 52/53 tax year financials.

You can view the contents of the each subject in one of two ways:

1. Open up Excel, and invoke the Excel Add-In to start with default templates, and then add or subtract fields from the Add-In QQube Add-In For Excel
2. Open up a more polished example directly from the QQube Configuration Tool, where you can also use the Excel Add-In to add or subtract fields.

We tend to stay away from terms like tables, relationships, mapping, reverse engineering, as QQube was built purposely to avoid that terminology, so that people can just drag and drop fields to get what they want.   Subjects are complete unto themselves.

We have two documents on this.

1. Understanding QQube Best place to get started
2. Advanced Users and Power BI Developers For those that like to get involved with the details.

We always say the best way to learn QQube is just to load the data, and open up any Excel Example, and add or subtract fields - most times it is better to filter your pivot table for one particular transaction, and then match the fields you use in the Excel Add-In with what you see on the QB screen.

Our founder, when speaking about QQube before, is often asked the question "how long does it take to learn QQube?"  and he always answers "QQube doesn't take long to learn - with the exception of maybe several dozen fields out of 5,000 which are put in the tool for a specific purpose; it is the tool that you use with QQube that becomes the learning curve, whether it is Excel Pivot Tables, Access, Crystal Reports, PowerPivot, PowerBI, etc..  We do the hard part of transforming raw spaghetti from QB into usable fields; but the last mile is in the report writer of choice".

Thank you for so fast reply.

I have a concrete question.

Say i want to build a dashboard/report with time slicer where the end user will select the daily period for a list of KPIs, in my example AR turn-over, my steps are:

1. Find the AR balance at the beggining of period selected with a slicer

2. Find out AR balance end of period selected with a slicer

3. Find out the Sales in the same period

4. Create a measure that will calculate AR turnover (assume all are credit sales)

So item 3 I can use GL details and filter by date which is linked in a slicer. Item 4 it is creating a measure in PowerPivot.

My challange is item 1&2. I can find the AR movement in a period instead using balances. Would this be the correct approach? So I will need jus SUBJECT GL details and the Calendar dimension.

Thanks

I don't how you would create dynamic calculation based upon a filter (slicer), but if anybody would know how it is the folks https://p3adaptive.com   The owner of that site was a product manager with Microsoft on the PowerPivot project, and he as a team of people monitoring their forums there.  They are also 'knowledge advisors' for our product - so they know both sides of the equation.

You would use the Trial Balance Activity subject.  Each period reflects the ending balance for that period - including the A/R account(s) and the Income Accounts.  But I would use the activity column for determining the income amounts, as they will be able to "cross" from one year to the next.

The question to pose to Rob Collie at PowerPivotPro, is if there is a way to create the measure on the fly.

A/R turnover is net credit sales activity for a particular period divided by (add begin and end receivables / 2)  All three elements are dependent upon the same period filters.

If you find out how that is done, please post back here.  I could see something like this in Crystal being pretty easy, but for PowerPivot, it depends upon the ability of that technology.

Ruslan,

in Powerpivot, for AR balance just create a Sum(amount) measure where Date is less than beginning of period and then another Sum(Amount) for Date is less then Ending Measure, filtered for AR accounts.  .or you could use a DatesBetween Dax function. 

I like you, do not like the "Period 34" notations in the Financial Summary files and have gotten around it by  settting up a "Period table" where each period has a "date". I have a starting Parameter, that user needs to add to worksheet that represents Period 1.(which is from the Config tool).  the Period Table is then linked to the Calendar and you have the full power of PowerPivot calendar functions. 

I bring in the Financial Summary view, in Power Query, unpivot it, so I have Periods down the rows. I parse the "Period" column so that I have a Period nn and a Type column (Budget, Actuals)... 

It sounds like alot but very quick to set up and it gives me much more formatting options and freedom in my PP measures. 

Anyway..thats my suggestion..

Fran

Ruslan,

You would basically create a disconnected table for your start and end slicer dates...

Here is a link to a blog at powerpivotpro for generating:  http://www.powerpivotpro.com/2011/04/ab-campaign-analysis-with-start-end-date-slicers/

I have done on reports and it works great. 

I am an advid fan of PowerPivotPro. Their book on PowerPivot has been invaluable to me.

Hope that helps.

Fran