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.

Share this Article

Search Articles

Filter Articles

CRM Tech DocMan

Recent Articles

Dynamics 365 Marketing vs ClickDimensions It’s time to pause, reflect and acknowledge a new era of inclusivity and collaboration. Part 2 - How to get the most from a Technology Expert – Asset Management Hub Property & Asset Management Hub Part 1 – Balancing CRM and Asset Management Scopes - Asset Management Hub Creating a Multi-Lingual PowerApps Portal How to Set Up a Microsoft Teams Site Using DocDrive365 Microsoft Teams - Adding a Microsoft Teams URL to a Dynamics Appointment Dynamics 365 Marketing – Customer Voice Survey Not Appearing In Emails? Using SQL Management Studio to connect to the Dynamics DB Calling a Power Platform AI Builder Model via oData How to use DocDrive365 to integrate permissions between Business Units in Dynamics with Sites in SharePoint Getting started with the Power Platform AI Builder. Power Apps Portal Information Hub DocDrive365 Security: Day One - Getting Started with Dynamics to SharePoint Permissions Part 5 - Power Apps Portals: How To Connect Azure B2C With Linked-In Part 4 – Power Apps Portals: Styling Azure B2C for Power Apps Portals The 3 Phases for Using Multi-Select Option Sets in Flow with Microsoft Forms Part 3 – PowerApps Portals: Azure B2C and Power Apps Portals – User Flow for Signup and Signin Part 2 - Power Apps Portals: New Application Registration in Azure B2C for our Power Apps Portal Part 1 – Power Apps Portals: Creating a New Azure AD B2C Tenant The Automation Bot: Launching Contextual Flow from Teams Creating a New Bot for Teams Debugging your Teams Bot using Ngrok Adding a Microsoft Teams URL to a Dynamics Appointment
Contact Us

Want expert advice or a demo?

Get in touch now and see how we can help your business grow.

  • Name
  • Email Address
  • Phone Number
 
Close

Understanding Your Challenges

Our strong understanding of CRM and emerging technologies within the Microsoft environment means we deliver the right solutions for you.

Proven Real-World Solutions

As a leader in the field of Dynamics solutions, our pedigree developing and delivering real-world solutions is unsurpassed.

Long Term Support

We provide support beyond our design, implementation and 'go-live' delivery using Sprints and continual updates to our AppSource apps.

CRMCS | Design by Thinktank Marketing | Citrus-Lime Limited

To improve your experience today and in the future, this site uses cookies. Read our full Privacy Policy & Cookie information here I Understand