Support Portal ContactGet in touch

Using Qlikview to Manage Properties by Price Reductions

   Words by CRM Consultancy

   on 18/05/2016 13:17:00

image

True Management Reporting involves not just giving a User a simple static Report – but instead providing the series of tools that allows a User to sit above the stream of data and ask questions key to their business and the Key Performance Metrics they needs to be testing against.

Qlikview is a brilliant tool for this purpose, and can produce tremendously powerful Dashboards for high-level Management Reporting.

This article is aimed at looking at how we do this in Qlikview, starting with a simple example of how to join two DataSets into a single Table, make this Table into a Filtered Selection, and then how this scales up into a full Dashboard.  The background to this article is focusing on how we might produce a Property Stock Dashboard for managing a Property Portfolio on behalf of a Client.

So to begin, say we have an existing query that retrieves the Full List of Stock from our Property Management System (say for example, Dynamics CRM) and into our Qlikview Dashboard.

clip_image001

This is stored in the ‘Main’ Tab of our Script for the Dashboard, and reads a flat-form of the CRM Data using a SQL View that we have previously developed to serve as a Full-Stock View.

Our requirement is to then add to this Dashboard to give us a way of filtering on Price Changes – so we can see the total number of Price Changes for each Property, and filter on the average number of Price Changes prior to Sale.

To do this, we initially look at adding a 2nd Script to our Dashboard to pull down the Price Change records from CRM into our Dashboard:

clip_image002

So we add a 2nd Tab to our Script (as way of separating the script sections into logical areas for good coding) and then develop the script for this second tab.

1. Read from the list of Filtered ‘cw_pricemove’ records

2. Look at which columns we want to bring into our Dashboard

3. Look at which conditions we want if we only want a conditional set of records brought into our Dashboard

4. Use a Key Field to build an association from our 1st script to our 2nd script – in our case, using the %CaseId to associate each Price Change to a particular Property.

This Key Field is the key to ensuring a logical set of relationships are built in our Dashboard, to give us the relational view of data we need for a strong dashboard.

We can see this association in the Qlikview Table Viewer:

clip_image004

Once done, we can ask Qlikview to load the Data into our Dashboard (following our scripts) and then look at how we use this data.

NOTE: Any problems or errors in our scripts will be found by this Load operation, and so may involve debugging the scripts to ensure any SQL or References are present

In our Dashboard, we can then present our data in the form of a simple Table or Chart.

So to build a very simple Dashboard, we could add a Straight Table Chart and then present our fields as Dimensions of the Table:

clip_image006

Once we have a fixed set of Dimensions, we can also add calculated Expressions to our Table as well – and this allows us to start drawing upon data through the associations in our data.

So we could add an Expression Column to Count the number of Price Change records are present for that Property:

=Count(PriceChangeDate)

clip_image008

This would then give us a calculated count of the Price Change records attached to the Property through our %CaseId Relationship we specified in our Script.

clip_image010

Which as an end result in our Dashboard, would give us a Table of Properties (i.e. Stock) vs the aggregate Number of Price Changes:

clip_image012

This is fine for a simple count of Price Changes – however our Property System may typically count the initial Price Change (of setting the Asking Price) which may not want our report to include.

To do this in Qlikview, we have two options:

1. Add a Selection Filter to our Dashboard – that then ‘tunes out’ any non Price Change records from the User’s view of the Dashboard and so from the Total displayed by the Table.

    • To do this, we would add a new List Box and specify [Price Change Type] as the criteria for selection:

Add the List Box

Use the List Box in the Dashboard to select the type of Price Change we want

image image
    • This then allows us to select ‘Price Reduction’ to then have our Number of Price Changes Expression only counting these selected Price Change records.
    • This gives us a more flexible but less hard-coded Dashboard – which is great for Power Users who will be comfortable slicing and dicing the data in the Dashboard, but less great for day-to-day users who may only be concerned with Price Reductions and so never need this flexibility.

2. Make our Expression Conditional – the other approach is to look at the Formula in our Number of Price Changes Expression.

    • So changing our simple formula from: =Count(PriceChangeDate)
    • To include a condition IF: =SUM(IF(PriceChangeType='Price Reduction',1,0))
    • This then forces the calculation within our Table to only Count Price Reductions regardless of the Selections made by the User in the Dashboard
    • This is clearly simpler on the user of the Dashboard, but gives less ability to ask questions of the Dashboard.

Regardless, both approaches help us build the report we want – looking at the number of Price Changes for a Property.

NOTE: Typically we would then look at only SSTC or Exchanged Properties to review the average Number of Price Changes involved in successfully selling the Property as way of reviewing our Clients Portfolio

This gives us a basic report, however the true power in Management Reporting through Qlikview is providing a more nuanced view of the data that allows for deeper degree of analysis.

To this end, we could combine the two approaches above and look at providing a Selection that allows the User to Filter the Properties by the Number of Price Changes involved.

To do this, we would add a New Selection Filter via a Listbox but then (rather than select a simple Field) provide the Calculated Expression as the options for the Listbox:

image

Script for our Expression: =IF(IsNum(Aggr(SUM(IF(PriceChangeType='Price Reduction',1,0)),%CaseId)),
    Aggr(SUM(IF(PriceChangeType='Price Reduction',1,0)),%CaseId), 0)

Using this Expression within a Listbox then gives us a powerful Selection Criteria for our End Users to query the Dashboard:

image

The User of our Dashboard can then query the list of Properties by the Number of Price Reductions, slicing the data accordingly to review from a Management Reporting perspective.

This approach is where the true power of Qlikview comes into play to build true Management Dashboards, as we can incorporate numerous Selections and Listboxes to allow the User to ask complex questions of the Data Model – allowing the User to conduct their own reporting on Trends and Analysis.

image

In the above example we can then have Selections for the different Key Dates that a Property may go through – Instruction from Client, Possession / Receipt of Keys, On-Market, SSTC, Exchange and Completion; and then Selections for the Type of Estate Agents or Auctioneers used to Market and Sale the Property, alongside our new selection for the Number of Price Changes.

This allows the Property Manager to review their Portfolio, ask questions of the data, and then ultimately jump into our initial Straight Table Chart as way of viewing the List of Properties and Columns they may specifically be interested in.

Giving us a true Management Reporting Dashboard through Qlikview.

  • "Paul has made a real difference to how my team of 24 people record and store valuable customer data and sales opportunities. Highly recommended."

    James, Operations Director

  • "Understanding your business allows us to advise when to implement aspects of CRM and, likewise, when not to."

    Paul McQuillan, Managing Director

  • "Dynamics 365 and CRMCS have made a real lasting difference to our business, allowing us to replace older systems that were holding back our performance."

    Grahame, Chief Operating Officer

  • "James worked well with us to help connect CRM with Outlook and relate how this might benefit our team using CRM for Property Care."

    Natalie, Property Care Supervisor

  • "Matt was really good with helping us run User Testing on the new Compliance Module of our CRM System."

    Tom, Compliance Administrator

Prefer to go old-school?

Write to us using the below addresses.

Head Office
CRM Consultancy
61 Oxford Street
Manchester
M1 6EQ

London Office
CRM Consultancy London
Grosvenor Avenue
London

Content © CRM Consultancy.