## Analyzing Customer Loan QlikView Project

The main aim of developing this Analysing Customer Loan QlikView project is to provide an easy way to extract, transform and load the information from the Microsoft Excel Sheet to remove synthetic key using renaming field name and concatenation.

Here we are creating KPI’s to calculate customers number, disbursed amount, Over Due Principle amount, Outstanding Principle amount and sales on year wise, month wise, etc.

We can create Dashboards and analyze Customer Loan Data by using different types of Charts example Bar Chart, Pie Chart, Line chart, Pivot Table and Straight Table.

Calculate the below:

1) Minimum and Maximum customer values
2) Minimum and maximum Disbursed Amount Month Wise, Year wise.
3) Minimum and maximum Due Principle

Using Set Analysis to Analyze & Calculate the Data:

State and region wise Loan Analysis using Pie & Bar Charts:

Area wise Distinct Customer Loan Analysis:

## Product Sales Analysis Qlikview Project

Project name: Sales Analysis

The main aim of developing this Product sales analysis QlikView application is to analyze the total sales data in a company year wise, month wise and week wise and day wise.

This analysis includes the ratio of sales data, profit maximization, etc.

The Analysis has done with the below 4 Charts namely Bar Chart, Pie Chart, Pivot Table and Straight Table.,

Bar Chart:

A bar graph shows comparisons among discrete categories. One axis of the chart shows the specific categories being compared, and the other axis represents a measured value.

In the above screen, it shows the different types to represent the Bar Chart (Sum of Sales per Year)

Pie Chart:

A pie chart (or a circle chart) is a circular statistical graphic which is divided into slices to illustrate the numerical proportion

In the above Screen, it shows the different ways to represent the Pie chart (Sales per Year)

Pivot Table:

Pivot Tables are widely used in data analysis to present the sum of values across many dimensions available in the data.

Straight Table:

Straight Tables are the most widely used sheet object to display data in QlikView. They are very simple yet powerful with features like column rearrangement, sorting and coloring the background etc.

## Analyzing Online Shopping Data QlikView Project

In this Analyzing Online Shopping Data application load the data from MS Excel and Resolved Synthetic keys with Concatenate and by renaming the field names.

We are using different sheet objects like the List box, Input box, Straight table, current selection and Charts (Bar Chart, Pie Chart, and Line chart).

List box: A list box represents the list of all the values of a specific field.

Current selections box: In the current selections box, selections are listed by field name and field value.

Search Object: Search objects can be used for searching for information anywhere in the document.

In this QlikView application, we are implemented four functionalities

1. Return Analysis
2. Shipping Analysis
3. Report
4. Comparative analysis

Return Analysis:

In Return Analysis calculate a total number of orders and returns.

We create fast change. Fast change to convert bar chart to line chart. Bar chart analyzes Region Wise Returns and line chart is primarily suitable when you want to visualize trends and movements over the region.

Use Pie Chart to represents a count of return value percentage in a circle with different colors.

Shipping Analysis

In Shipping Analysis to calculate the total number of shipped orders on region wise.

In Bar chart use ship mode and product category to calculate region wise shipped product sales and orders.

We can use a pie chart to calculate the percentage of shipped orders in region wise.

Report

In report calculate total no of sales and returns based on product category and region wise.

Comparative analysis

The comparative analysis we are going to compare state wise distinct customers.

## QlikView Real Time Projects and Scenarios

Qlik Training Module

1)    Qlikview  Server

1. Qlikview Server components
• QMC
1. a)Technical brief about Qlikview Architecture
• Webserver
• QVS
• Qlikview Desktop
1. Services installed on Qlikview Server
• Qlikview Directory Service Connector
• Qlikview Distribution Service
• Qlikview Management Service
• Qlikview Server
• Qlikview Webserver
1. Qlikview Management Console
• Mount a folder for deploying Qlikview files (User Document)
• Connection to Active directory, LPAD
• To add or remove user from document security tab
• Availability of document to users
1. a)Access Point
2. b)Mobile / Tablet
• Timeout settings for Qlikview document
• Check Qlikview Active Users
• Authentication for Qlikview Users
1. Qlikview Logs
• Qlikview Server log
• Webserver log
• Directory connector log
• QMC log
1. Qlikview Webserver
• Access point
• Single Sign On
1. Ports to run Qlikview services

2)    Data Modelling

• Dimensions
• Facts
• Schemas
• Best Practices
• Normalization

3)    Qlikview Introduction

• Qlikview features
• Value creation
• Story telling
• How to think while designing apps?
• Concepts of BI
• Tools (Tableau vs Qlik), power BI

4)    QlikviewImplementing Architecture

1)      Database Connectivity

• SQL Queries
• SQL Tables
• ODBC/JDBC
• DSN Creation

2)       Qlikview Memory Management

3)      QVD Layer

• Database connection in Qlikview
• Fetching Data from Qlikview
• Generations of QVD
• Fetch data from other data sources in Qlik

4)      Transformation Layer

• Identifying Facts and dimensions
• Creation of Data Model
• Scripting techniques
• Optimization in scripting
• Creating Calendar

5)      Visualization

• Identifying KPIS for the business
• Layouts and colours
• Bookmark
• Extensions

6)      Deployment Process

5)    Set Analysis and Functions

1)      Point in Time Reporting

• Current Year
• Previous Year
• YTD
• MTD

2)      Chart Function

• Range
• Aggr
• Class
• Pick
• Above
• Below

3)      Scripting Functions

• Control Statement
1. a)If else
2. b)For loop
3. c)Switch Case
• Other Functions
1. a)Apply Map
2. b)Match
3. c)Look up
4. d)Wild match
5. e)Interval match
6. f)FirstsortedValue
7. g)FieldValue
8. h)Exists
9. i)Isnull
10. j)Date functions
11. k)Time functions
12. l)Aggregated functions
13. m)Peek
14. n)Previous
15. o)Rec no
16. p)Row no

• Techniques
1. a)Joins
2. b)Group By
3. c)Order By
4. d)Keep
5. e)Concatenation
6. f)Variables
7. g)Let
8. h)Set
• Pnl Template Explanation

6)    Qlik Project Management Process

1)      Business Understanding – Ground work to be done before project

• Domain/Sector Understanding
• Understanding the specific processes which generate the required data (for e.g. Billing Process in a hospital)
• Understanding the technology (ERP sector) which capture data from a business stand point
• Clearly understanding the KPIs and the business terminology

2)      Requirement Gathering

• Reports
• KPIs
• Presentations
• Email
• Daily/Monthly/YTD views
• Dashboards
• User Role Specific
• Document Template

3)      Technical Architecture

• Server configuration
• Ports
• Authentication

4)      Development Governance

• Status Reports – Weekly (formats attached)
• Tracking the project plan
• Timesheet

5)      Testing

• Unit Testing – at the time of development
• Test Cases formats

6)      UAT

7)      User Presentation/Demos

8)      KT and Adoption Techniques and Support related

• Detailing of each charts – Dashboard
• CBT

7)    Qlik Visualization

1)      Qlik Visualization Concepts

• Variables
• Example Chart with all properties used including benchmarking
• Qlik Properties
• Sample Application
• Extensions (deployment etc.) in Qliksense
• Document properties & Sheet properties
• Sample Application for Both Qlikview/Qliksense

2)      Things to check

• Spell check
• Font Consistency
• Colour
• Layout Consistency
• Chart Text

8)    Optimization and Best Practices

• Scripting End
• Visualization End
• Server Performance Side
• Performance Turning
• Qlikview Analyser
• QlikView Governance Dashboard

9)    Qlik Support and Bug Finding

Qlik community:

1. What is Qlik community?
2. How it is useful?
3. How to register?

Branch.Qlik.com:

1. What is Branch Qlik.com?
2. How to use it and for what?

Support Track

Debugging Issues

Data warehouse Concepts:

•  Basics of Data Warehousing.
• What is ETL. (DaraMerging, DataAggrygation, Datascrubbing, Data cleansing)
• Data warehouse Architecture
• Data mart

Dimensional Modeling Concepts:

•  Introduction to Dimensional Modeling.
• Conceptual Data Modeling, Logical Data Modeling, Physical Data Modeling
• Dimensional table
• Fact table
• Star Schema
• Snowflake Schema
• Star schema Vs. Snowflake Schema
• Fact Vs. Dimension table
• Fact Vs. Aggregate Table
• Types of Facts
• Types of Fact tables
• Types of Dimensions
• Level of Granularity
• Slowly Changing Dimensions( SCD )
• Normalization De Normalization

QlikView Introduction:

• What is QlikView?
• Why QlikView is most effective than other BI Tools.
• QlikView Architecture.
• QlikView Components.
• Qlikview Desktop Developer.
• How data flows in QlikView.

Scripting:

• Creating Etractor
• Concatenate.
• NoConcatenate
• Auto Concatenate
• Joins Vs Keep
• Look up
• Exist  & Not Exist
• Set Analysis
• Security
• Section access
• Interval match
• Master Calendar
• Basic Aggregation Functions.
• String Function.
• Counter Functions.

Modeling:

• Table Viewer.
• Modeling Techniques and Best Practices.
• Creating Star Schema
• Creating SnowFlake Schema.

QVD File:

• How to create a QVD File.
• Reading data from QVD Files.
• Storing QVD By Variable.

• Insert.
• Incremental Load through a QVD File.

Hierarchies:

• Drill Down group.
• Cyclic Group WithExpressions.

QlikView -Set Analysis and Alternate State:

Transformations in QlikView:

• User Preferences.
• Document Properties.
• Sheet Properties.
• Sheet Objects:
1. List Box.
2. Statistic Box.
3. Multi Box.
4. Table Box.
5. Charts.
6. Input Box.
7. Current Selections Box.
8. Buttons.
9. Text Object.
10. Bookmark Object.

Other Features we provide:

• Real Time Scenarios
• Resume Preparation
• Provide Sample Resumes
• Interview FAQ’S
• Conduction Mock Interviews
• Class notes which is useful for who is trying for job.