Categories
Common

ToDo

Check ReportsUpdate for is_array();

ReportsController::ActionCreate() is not working correct (missing 1:n day assignment and wrong redirect

Categories
Common pepeQvDocumentService /en QlikView /en

new SOAP method for QlikView Objects

So, as I currently work on a solution, where you will be able to implement a graph from QlikView into a webpage (as bmp or gif). I needed to get the ObjectID’s of a specific QlikView Document. So reading the API i can now inroduce the new funtion:

getbeeyeObjectList(_docName)

This returns an Object with the following parameters:

  1. _docName
  2. _ObjectID
  3. _SheetID

With this combination, it is possible to refenrence any object by the SOAP Service;)

Categories
Common QlikView /en

QV Mailer Service Web Frontend Functions

Mail Administration Interface

For the distribution of QlikView PDF Reports via Mail, I build and am building an interface that implements the following functionalities:

  • User Management
  • Report Management
    • Including Recipient Selections
    • Including Filter Builder
    • Individual Mailing of Report (PUSH)
    • Individual Mailing of Reports (PULL)

The system is keeped clean and simple! I will post some screenshots to make it more “visible”.

Categories
Common

General Design of QvDocumentService

General

Many functions of the qlikview API are interesting, but only available when you run qlikview on your local Windows PC. Sometimes it would be very useful to have Document Informations available without QlikView installed or start e.g. a Print Job by an remote call.

To use the api more user friendly, I implemented an SOAP-Service as wrapper, that allows users with access to the SOAP-Server to use some of the basic informations and functionallities. This means:

  • Getting a list of available fiels inside a qv-document
  • Getting a list of available values inside a field of a qv-document
  • Getting the current selection of a field (values)
  • Getting a list of available Reports
  • Starting a print-job while adressing an url

As it should be easy to use, I build up an webadministration backend based on yii – www.yiiframework.com – which can be easily installed on an xampp – sytem. – www.appachefriends.org

I will put up an detailled installation description in a seperate page.

Class design

Object:

beeyeQvDocument

  • _DocName
  • _ListOfFields
  • _ListOfValues

Children:

  • beeyeQvField
    • _DocName
    • _FieldName
  • beeyeQvValue
    • _DocName
    • _FieldName
    • _FieldValue

Methods:

beeyegetQvFields(_docName)
beeyegetQvValues(_docName,_fieldName,_searchString (optional))
beeyegetQvReports(_docName)
beeyegetQvBookmarks(_docName)

Categories
chapter 4 net income and cashflow /en

indirect cashflow

To display the saldation of an single account of the balance, it is important to understand the following to logics:

  1. Opening and closing balance for a single account
  2. Timeshift calculation for a single value

The best way to explain this behaviour is to give you an short example. At the beginning of our new forecasting scenario we have a start value of 10 000.- CUR that we call the “opening value” on our accounts recievable, according to our profit and loss statement, we’ll have a growth of 30 000.- CUR for this period. So thinking about a time shift of 0 days “the closing” value for the periode will be 10 000.- CUR + 30 000.- CUR = 40 000.- CUR. So as we have no difference between the generated profit and loss income and the balance, we will not have an effect for the cashflow… The bank account raised the same amount (30 000.- CUR) in the same periode.

According to our experience, the money,. calculated in the profit and loss statement will flow in with different delays. To keep our example simple, we calculate a delay of 15 days. This means, if we plan 30 000.-CUR in a period month, half of the 30 000.- will apear in the same period, the rest in the next… What sounds simple, isn’t as simple to show in a database – you have to take care, if values already exist or what happens when the delay is 10 days or 90 days??? The best way to discibe it, is the way how it’ll be calculated in detail:

Claculation of timeshift

Basic: period, month
Value: 30000 CUR
shift: 15 days

So we first have to calculate the weight for the smallest time unit (day), what means 30000/30 = 1000 CUR.

The next step will be to define the value for the current month: (30-15)*1000 CUR.

We keep 15 * 1000 CUR in Mind for the next period!

So taking a look at the following table we’ll see the following results:

Period Opening Balance added values Time Shift Value of Period Closing Balance
2011 0 30000 15 30000 30000
1 0 30000 15 15000 15000
2 15000 0 15 15000 30000
3 30000 0 15 0 30000
Categories
chapter 4 net income and cashflow /en

basic thinking

Before we start to develop the logic for a cashflow or netincome corporate planning solution. We need to understand the basic economic rules that underlay this issue.

In the corporate world there are two worlds of watching a company. The “dreamy profit and loss view, where people think that money and cash flows directly (no time between writing an invoice and recieving the money) and the “realcashflow taking care about time delays for spending and revieving money in a company.

To build a solution in a database, taking care about those two worlds – we have to define a flag or sign, in which world each single value belongs. This means we build 5 types of values:

  1. NEUTRAL – Helper Fields, to build logics like amount * price = revenues -> so price is a helper field!
  2. TAX IN – Those Fields really contain an amount, that is a relevant profit and loss amount (e.g. revenues)
  3. TAX OUT – Fields like goods needed to build a product
  4. CASH IN – real payables calculated on the balance accounts-recievable
  5. CASH OUT – real payables calculated on the balance accounts-payable

Having those flags integrated, we have the possibility to sum up all TAX IN and TAX OUT, then build the saldation TI – TO = NI and we’ll become the NetIncome for a individual subset of the data.

Categories
first sample /en intro /en

simple start

example

Bevore we start with complex planning issues, it´s important to understand the basics of the “logic” in the tool! So we build up a small personal budget where we take care about 2 income accounts (Salary and Family) and 4 expense accounts (Rent, Food, Fun and Communication).

identify dimensions

For every dataset, a basic dimension is needed, here we call it “Basic Dimension“. Additional we need two more dimensions for “Income” and another for “Expense“.

Inside the Dimensions we need the following Dimension-Elements:

Basic Dimension

  • My Personal Budget

Income

  • Salary
  • Family

Expense

  • Rent
  • Food
  • Fun
  • Communication

Additional we need to define the relationship between the dimensions:

  1. Root – Basic Dimension
    1. Child – Income
    2. Child – Expense

This will build up an “virtual” tree looking like this:

  • My Personal Budget
    • Salary
    • Family
    • Rent
    • Food
    • Fun
    • Communication

Later you´ll see, that we have several visual helpers do divide them from each other. Those depend on the logical type of the related dimension. Those individual logics will be called structurelogic and we will use 3 seperate logics for our dimensions.

create structurlogic (calculation rules)

To make it simple, we only want to have a fact field, that allows to enter an absolut value for an individual period. This means, we build the following 3 structurtypes containing one fact field.

  1. Company
    1. no factfield, only for aggregation
  2. Income
    1. factfield, revenue (float)
  3. Expense
    1. factfield, expense (float)

Later you’ll see how to build a calculation of IncomeExpense.

periods

“The only limitation is time” – it’s a fact, that time is the limited fact in a human live. So nearly everything is adjusted to timemanagament and the available ressources for the focused moment. For our budget we want to focus on months as smallest viewport and the year as aggreagtion of the individual months. The framework has a special handling of periods, that you need to know for further datasets:

Weeks:

  • -10001,-10002,…..,-10048 (All weeks from the first year, 7.5 days per week)
  • 1,2,….,12 (All months from the first year, 30 days per month)
  • 1,-2,-3,-4 (All quaters from the first year, 3 months, 12 weeks, 90 days per quater)
  • 10001 (the first year, 12 months, 4 quaters, 48 weeks, 360 days)
Categories
intro /en prepare your system /en

prepare your system

after setup

After you have installed the framework and the tool, you have a “clean” system that needs some settings to run! So first of all you need to login as poweruser and enable the needed module for this user.

Continue as followed:

  1. Go to the mainmenu “planlogiq” – “My Account” – “All Users
  2. Select the user you want to enable modules for
  3. jump to the tab – “user rights” and edit the line “mod_planung” just check the “read” checkbox
  4. reload the complete site by hitting “F5” or the “reload“-Button of your Browser

At the mainmenu you will now see a new register “Planning“. Open it and go to “Administration” – “Dimensionsmanagement“. Now you can continue to read the next top from intro.

Categories
Common

Hello world!

Hello, this blog is a mix between a blog and a book, which is related to the open source corporate planning framework planlogiq.

You can download it from here: http://www.planlogiq.com

It’s based upon PRADO PHP Framework – http://www.pradosoft.com – UI DHTMLX – http://www.dhtmlx.com – and MySQL – http://www.mysql.com . All examples and textes are published under the CCL Commen Creative License and are published by Philipp Frenzel (pf(at)com[minus]x[minus]cha.com).

If you have any suggestions or feedback, feel free to contace me!