1. Perpare the data: Import Delimited Flat file
2. Create New Analyses
3. Publish a Dashboard (No user access management)

Prerequisite

• AWS Account
• Amazon QuickSight Account

You can signup Amazon Web Services for free. However, for QuickSight there are two options. Standard and Enterprise. Standard comes with a $9 per month per user. You also get two month trial period to evaluate the product.

When you signup for QuickSight you get the below screen. If you were going to just experiment like I do, please choose IAM federated identities only.

I also did not choose other options of connecting to other AWS services.

Once you complete the form and submit, you will see a screen like below and you are good to go.

Amazon BI suite and scope for this blog

AWS BI suite has lot of tools that need to be used in tandem for better outcome. Some of the tools that I came across are

1. AWS Glue – a complete ETL tool
2. AWS Glue Data Brew – Meant for non-IT users to prepare their data for analytics
3. Amazon QuickSight – The business intelligence tool for analysis

For this blog, we will focus on Amazon QuickSight and use the simple file that was generated for Qlik Sense and Power BI using Talend.

Amazon QuickSight

The home or the hub

The hub or the home page of Amazon Quicksight has three sections.

1. Dashboard
2. Analyses
3. DataSets

The separation of dashboard and analyses is unique to Quicksight though it may resemble report vs dashboards of Power BI. The data gets refreshed in the analyses section continuously and you have to publish the analyses to update the dashboard. This gives an unique advantage. The analyses section acts like a staging area where you can validate if the data looks good before you publish it to the end users. However, there must be an automatic way to deploy dashboards using APIs (yet to explore). All user access details are added to the dashboards.

Prepare the data: Import Delimited Flat file

For the Odoo Point of Sale experiment, we have three flat files with columns de-limited by a semi-colon. They are

1. orders
2. orderline – that describes each items in the order and order quantity
3. products – contains details of the products in each order

When importing the orders file, the data looked like below. As you can see, the default delimites is comma (,). So the file is not read correctly. This can be resolved by clicking teh Edit settings and prepare data button.

Clicking the arrow of the orders file as shown below will bring another pop-up that lets you set delimiter and header details.

Once all the details are set and the pop-up closed, QuickSight will identify all the columns as shown below

Import and join other files

All the three files can be imported using the same technique and can be joined visually. QuickSight lets you modify join as required using drag and drop.

Create New Analyses

The actual build of dashboard happens in the Analyses section. You can start a new analyses

1. From the hub
   1. Click on New Analyses button
   2. Click on a data set and then Create Analysis button on the pop-up
2. From within the dataset
   1. Click on Save & Visualize button on the top right

A data set can be added to an existing analysis as shown below. Click on the pencil icon highlighted and the pop-up will show up to manage the datasets in the analyses.

Once the data set is added, the columns are displayed on the left. Now you can simply click on the fields and Amazon QuickSight will automatically create visualizations for you. You can also modify them. We will see some of the key components of the Analyses layout

1. Left Navigation: This contains visualizations, filters and parameters, actions. Also you can use the add button above the left pane to add calculated field or parameter at the analyses level
2. Left Pane: This is a dynamic pane that changes according to the left navigation selection and for visualization it shows the datasets, list of fields for the selected data sets and the visualizations.
3. Field Well: This is the top portion and shows the list of fields we use in a visualization. We can format the fields by clicking on the arrow of the fields.

Publish a Dashboard

Once you have created an analyses, you can simply use the share button on the top right corner to publish the dashboard. You can either replace an existing dashboard or publish it in a new name.

There are also other options available for you at the time of publish that are self-explanatory.

Once you publish the dashboard, it will be available in the Dashboard section of the hub. These dashboards are read only and so it will not have the development components that we saw in the analyses layout. This makes the dashboard look neat by occupying a large screen space. This is a simple dashboard created to demonstrate basic capabilities of QuickSight.

The post First Dashboard using Amazon QuickSight appeared first on CodeWander.

Continuous Learning: Exploring alternatives

This is one of the greatest means that has helped me personally to stay away from boredom. In fact, this has helped me in lot of role transitions. Try to learn about emerging trends and how the entire industry is moving forward. Learn more about the competitors of the product/ framework that is used at work. This will help one to get another perspective of things at work and also to get more new ideas that can be tried. The blogs and free webinars are now a great source of contents with different perspectives and can help guide towards the next thing one want to learn. One of easy way to do is to do a google search <<product name>> alternatives and <<product name>> open source alternatives. This easily gives a list of other tools in the area of interest.

Lifelong Learning: Side projects

This is an extension or application of the learning but worth mentioning as a separate point. Many times mind races ahead of time. We cannot try everything we want at work. The best way to nurture racing mind is to have side projects or little experiments that one can do. This can be a personal or work related project. This helps us to firm up learning and validate understanding. If its a personal project, a github page (for software) or a blog will help showcase them and enrich one’s profile. Personally I started writing blog, codewander.com, to record my thought process, understanding of technology, ability to solution so that I can refer it later.

Learning on the job: Role with a GAP 

It is one of the best advices that I received when I was offered full-time job post my MBA internship. The reason is simple. Its not only about what one has to fulfill and excel at the job (Contribute) , but also about what the job has to offer the individual(Learn and Grow). If there is no or narrow GAP one will easily get bored in some time. If the GAP is too wide, then that can set the person to fail at the job. Having the right mix of knowns and unknowns will help one to do well and grow in a role. If there are times when you do not seem to have an opportunity to learn or grow, that indicates the time to change role at work.

Collaborative Learning: Help Others: Get Versatile

This is another best way to become versatile and improve your competency. Volunteering to help others in the team or help another team to achieve their goal is a great way to learn things that one may not know otherwise. This also keeps one away from boredom while earning good relationship with the colleagues. Most of time when someone seeks help, they do not need an expert but rather someone who is willing to work together and solve the problem.

The post How NOT to be bored at Work? Keep Learning! appeared first on CodeWander.

The original survey and the article compares the opinion between those who love James Bond movies and the general population. However, for the challenge the context is changed from the original survey to comparison between Brexit voters: Remain vs Leave

The sample data was provided by Makeover Monday Team. The original visualization present in the article is below

Analysis

What works with this chart?

The article highlights what is preference of the people of Great Britain with respect to next James Bond character. The chart correctly sorts the category in the descending order of acceptance.

What does not work with this chart?

The visualization could have avoided the unacceptable and Don’t know. The Don’t Know component is very small in most categories and Acceptable percentage will easily give readers an idea of the unacceptable percentage.

Makeover Monday Tableau

Data Points

So in our case instead of general population we will compare Brexit Voters: Remain and Leave, only on Acceptable percentage to convey the message. We will basically keep only the enough information required to convey the message on what is acceptable to the survey participants.

Images Used

We used images to convey the context of the visualization rather than users having to read through.

Following are the image sources

• An article: Who will play the iconic character next?
• Hiclipart.com and pngguru.com

Final Output

Here is the final output. The live dashboard is available here: The Next James Bond

The post Makeover Monday 2020 Week 5 Tableau appeared first on CodeWander.

Download and Install

The extension can be downloaded using the link below

Download Qlik Sense Extension Scatter plot Multi Series

Extract and copy contents of the folder in the Extensions folder on your desktop or server. In Desktop, it usually is in the My Documents/Qlik/Sense/Extensions folder. you can also deploy using Qlik Management Console on the server.

Note: This extension refers to online cdn version of plotly and hence require internet connectivity.

Demo

Configuration

Measures and Dimensions

This extension requires only one dimension and an even number of measures to visualize data. The extension takes the odd measures as x-value and even measures as y-value. Each pair is a series. This design is to accommodate plotting data from different set of columns. If either your x or y value is from the same column, you can repeat the measures.

However, its important to note that the extension plots all measures using the same x and y scale/ axis. It does not support multi-axis. The extension currently supports ten measures which means you can plot utmost 5 series.

Settings

There are four sections under Appearances that helps us configure the appearance of the chart apart from the two common sections, namely General and Alternate States. They are

1. Series
2. X Axis
3. Y Axis
4. General Settings

Series

Series section lets you add details related to each of the series that you want to plot. It does not validate the number of series that you have defined in the measures section. Instead it assigns the series details in the order of appearance.

In the series section, you can enter the following for each of the series which means you can control each series independent of other series.

• name of the series
• color
• series type
• marker size
• highlight first point
  

Series Type: There are three options in the series type: lines, lines+markers and markers. The markers is the usual scatter plot where as lines and lines+markers lets us draw the line to the plot connecting the data points in the order they appear in the series. The following picture shows the lines+markers and markers series in one single visualization.

Highlight first point: This will be useful when we use the either line or lines+markers series type to identify where the series starts and helps in following the change in data points. When this option is checked, the first data point will have a black border around it as shown in the picture below.

X Axis and Y Axis

Both X-Axis and Y-Axis has the same set of parameters and are quite self-explanatory. This option lets you choose configure how the grid-lines, zero line, axis base line or the labels need to be plotted.

Show Line vs Show Zero Line

The Show Line is to show or hide the base line of the axis whereas Show Zero Line is to show or hide the line at the point zero. In the picture below, we can see that for the Y-Axis, we hide the axis line but show the zero line

General Settings

General Setting has two options. They are

• Display options for mode bar
• Show Legend

The mode bar is the list of buttons that appear on top. You can choose to have it or hide it using this option. Currently legends are shown on the right side vertically. I will add more options for the same eventually. Currently you can either show or hide the legend.

The post Qlik sense extension multi-series scatter plot appeared first on CodeWander.

We will makeover the visualization as below. In this blog we we will see how I arrived at the below visualization. You can also check out the live Tableau dashboard

Objective of Makeover Monday 2020 Week 4

1. What works and what doesn’t work with this chart?
2. How can you make it better?

Analysis

What works with this chart?

This chart does a good job of showing the global indicators when a country is selected and also the geo heatmap of a selected global indicator. It is easy to look at an individual country’s numbers and also the overall comparative view.

What does not work with this visualization?

1. The visualization does a poor job of highlighting the global indicator on the map when a country is selected. It still gives the heat map but zoomed to the country selected. This is of no use as there is no way to compare when the map is zoomed in.
2. The chart does not visualize the location of bridges on the map. For example, in the image below, it does not sow the geo location of the completed bridges in Ethiopia.

How can we make it better?

Remove Global Indicators from the map

Use table heatmap instead

First we will address the global indicator on the map that comes up when we select a country. We will instead use a simple heatmap table with a dropdown for the indicators. This simple heatmap will easily show us the comparative view and the sorting feature will enable us to find the ranking of the country easily. We will also use this a filter to select a specific country

Plot the location of bridges on the geo spatial map

Next we will address the missing feature, which is the plotting of bridges based on location. We will use the geo map to plot the location of the bridges and highlight the bridges in different colors based on the type of bridge. The size of the bubble shows the span of the bridge in meters. When we select a country, the map zooms in to that country and shows the bridges. When we deselect all the countries, that map zooms out and shows all the countries with bridge heat map on it. Please see below.

We will provide following additional information about the bridges. Number of Bridges by:

1. Type
2. Stage (Completed or not)
3. Year constructed

Retain the good feature: List Global Indicators for a Country

When we select a country we will also list all the global indicators of the country. When none of the countries is selected we will hide these global indicators as it does not make sense.

Final Output

The focus of the makeover is to ensure that we do not use more components than necessary and provide as much insights as possible. Thus putting all these components together, we get the below output. As you can see we also added a text object to give a brief introduction about Bridges2Prosperity organization. Please provide feedback on the analysis and also on the technical approach.

Tabeau Tips and Tricks

Since I am new to Tableau, I had to learn lot of new tricks to achieve this visualization. Some of the tricks are:

1. Using Parameters to dynamically change the measures
2. Use bar chart as KPI tile element
3. Using Dashboard Action to hide the Global indicators based on condition

Since this blog is mainly focused on non-technical aspect, I will write another blog that explains these technical details.

The post Makeover Monday Week 4 Tableau appeared first on CodeWander.

Objective of Makeover Monday 2020 Week 3

1. What works and what doesn’t work with this chart?
2. How can you make it better?

Analysis

My analysis is not about how much more information we could extract out of the data set but how much information is need to support or supplement the article.

What works with this chart?

The article talks about sugar consumption against the recommended amount. The chart does a good job in highlighting the recommended range and it quickly lets the reader know how much the sugar consumption is above the recommended range for different age groups.

What does not work with this chart?

In the chart there are three age groups that consume three times more sugar that the author mentions in the article. It takes some time to figure out the age groups that the author is referring to in the article.

How can we make it better?

Highlight the group/ category (children) in the chart

The chart presents the sugar consumed as percentage of total calories by age groups. The article explains that the kids consume three times more sugar than the recommended 5% of total calories. However the visualization does not highlight the age groups that are in discussion.

We can highlight the age group that the article focuses so that the readers can easily grasp the age groups.

Show the trend of Children Category

The article also states that the overall goal is to reduce child obesity

Why? There is a recognition that bold moves are needed if the ambition to halve the child obesity rate by 2030 is to be achieved.

Source: Is it time to treat sugar like smoking?

So the article is about reducing child obesity though the dataset contains trend for adults as well. In order to stay relevant to the article content and to support the argument, I will add the trend for the children that will be highlighted in the first chart.

Makeover Monday 2020 Week 3 – Final Output

So with these two charts we will have data that supports and supplements the content of the article and the emphasizes author’s point of view. The makeover is done using Tableau. The final output is as seen below.

You can also see the live dashboard in Tableau Pubic .

Please share your feedback on the same.

The post Makeover Monday 2020 Week 3 appeared first on CodeWander.

1. Set up data structure and property
2. Define Measures using Set Analysis
3. Highlight series using Color by Expression

For demonstration purpose, let us take an example lab experiment that measures the indoor/ outdoor temperature and humidity of a place. There are two instance of the experiment and 10 observations made in each instance. We will plot the indoor temperature against the indoor humidity from the two experiment and the final result will look like below

Set up data structure and property

We can use the manual entry feature of Qlik sense “Add Data” to create columns and actual data. The data looks like below. If you look at the data, the observation Id will be unique for each row and we will use that as the dimension. Now load the data, create a sheet and lets create the scatter plot.

Define Measures and Dimensions

Dimension

Drag and drop the scatter plot and add Observation Id as the dimension. Its not necessary that this dimension always has to be unique but in our example, this is the unique field that lets us lot each every row as a data point.

Measures

X-Axis: We will plot the indoor temperature in the X-Axis. Since there needs to be an aggregate value for the measure, we will use max() function. Since the observation id is unique we will get the actual value of each row

Y-Axis: We will plot the indoor humidity in the Y-Axis.

At this point, the graph looks like below: all data points (observations) plotted in a single color.

Highlight Series Using Color By Expression

Now in order to differentiate the experiment 1 and 2, we will use the “Color by Expression” under Appearance > Colors and Legend

We will use the Experiment field as below to differentiate each experiment series. And also, mark the expression as color code

=if (ExperiementId=1, 'red', 'blue')

Now the chart looks like below with the experiment 1 series in red and the experiment 2 series in blue. We can use this color code option to differentiate the data points into multiple series.

Demo

Limitations

Technically its still a single series that gets data getting data from one column for x-axis and another column for y-axis. It is not possible to plot data from different columns for each series. Thus in our example, we can not plot Outdoor data and indoor data in the same scatter plot.

Similar Tips: I have written another blog that explains how to color code combo chart conditionally without changing the color of line. Please check it out.

The post Qlik sense scatter plot mulitple series appeared first on CodeWander.

The Observations

I used 361 observations so that it is possible to plot circle. For this demo i uploaded a single column file with numbers from 0 to 360 into the table called Counter. But you can also create the table using generateSeries function.

Implement What if Analysis in Power BI using variables

In Power BI we have the concept called parameters that lets you give inputs to the dashboard.

The Parameters

Create following parameters using the New Parameter Option in the modelling tab. While adding the parameter, please check the option “Add Slicer to the Page”. This will add slicers that will help change parameters to the page.

Waves Parameters:

Circle Parameters:

Measures

Create the following measures for the waves

WaveTimeCounter = 1/ (360 / 'Seconds to Plot'[Seconds to Plot Value])  

Wave X = [WaveTimeCounter] * max(counter[Counter])  

Wave Y = if ([Shape Value]=0,WaveAmplitude[WaveAmplitude Value]* Sin(2 * Pi() * WaveFrequency[WaveFrequency Value]* (counter[Wave X])),WaveAmplitude[WaveAmplitude Value]* COS(2 * Pi() * WaveFrequency[WaveFrequency Value]* (counter[Wave X]))) 

Wave X is the measure used in X axis to plot waves.

WaveTimeCounter will determine the incremental value for the time depending on the number of seconds we choose to plot so that we distribute the time equally across the 361 observations. Since we use the counter as the dimension, we use max(counter) to get the single value to use in measure as measure expects only the single value.

Wave Y is the measure that determines whether to plot Sine or Cos wave depending on the value of the parameter “Shape”.

The Navigation

As mentioned earlier, we will use XY/ scatter chart to plot x and y values. Unlike Qlik sense, Power BI does not provide a capability to dynamically change the title or axis ranges of chart axis. Neither does it provide the capability to change the display text of slicers dynamically. Because of this we use two pages, one for waves and the other for circle. Then we use bookmark and button to switch between two pages.

To enable the bookmark pane, got to the view menu on the ribbon and check the bookmark pane as shown in the image below. The bookmark pane will appear and then you can go to the waves page and click “Add” in the bookmark pane and give it a name. Similarly you can do so for Circle Page also.

Now that we have created two pages with bookmarks, we create a button on each page. This button will have the action to go to the bookmark url. To insert button, go to Home menu on the top ribbon and click on Insert Button. When you select the button, in the visualization pane on the right, you will see the action section. Choose Type as bookmark and Bookmark as the other page.

Now you will be able to switch between two pages by “Ctrl+click” on the buttons.

The Graph

We have to create two graphs, one in Waves page and the other in Circle page. As mentioned earlier in this blog, we will use SV/ Scatter chart to plot the x and y values with same size for all the bubbles. In order to show the change in the wave or circle based on the changes in the parameters, we will have fixed predefined values for the axis ranges. An example is shown below whereFor example, we set the axis for the y axis of wave graph to be from -6 to +6. We have set the parameter range for amplitude to be a maximum of 5. So when we change the amplitude, we will be able to see the change in the height of the wave easily. If the range is set to auto then the scale will adjust and the wave height will always be the height of the chart.

Now drag and drop the Wave X and Wave Y in the Wave Graph and Circle X and Circle Y in the circle graph.

Changing the title of the wave chart

Since its not possible to change the title of the chart dynamically, we will have a workaround to achieve it. this is done by referring to the blog Dynamic Chart Titles in Power BI.

1. Create a measure called Wave Chart Title and use the Shape parameter to change its value as below.

Wave Chart Title = if ([Shape Value]=0 , " Sine Wave " , " Cos Wave "

2. Create a KPI visualization and place it on top of the chart and drop the Wave Chart Title in the field section of the visualization

The Final Output

That’s it! We have implemented a What-if Analysis in Power BI using parameters. The final output is a two page dashboard with parameters on the left and button on top right to switch between the Wave page and the Circle page. Then we have the chart title and chart that occupies the remaining of the screen. We wil then apply some formatting for individual components which is better in Power BI compared to Qlik Sense.

The post Implement What if Analysis in Power BI appeared first on CodeWander.

Basis of What-if Analysis

The basis of this What-If analysis is a formula or an equation. We will have a formula written using the parameters. Then we let users change the parameters and see the outcome of the equation.

Drawing Sine, Cosine Waves and Circle

In this blog we will see how based on the parameters we set the graph changes the type of wave/ shape and its characteristics. This is an inspiration from this video to create sine wave in excel

The latest working app can be found below
Download CodeWander Qlik Sense What if Analysis

For Wave, we will be able to set the following characteristics

1. Frequency
2. Amplitude
3. Number of Seconds to Plot

For circle, we will be able to set the following parameters

1. Center
2. Radius

Implement What-If Analysis in Qlik Sense

In Qlik Sense, we implement What-If Analysis using the variables and Variable Input component that is part of the Qlik Dashboard bundles that is shipped along with Qlik Sense. Variable is such a powerful feature of Qlik Sense that provides lot of application wide dynamic capabilities and alter the behavior of the application. I have already written two other Qlik Sense Tips using variables namely, Cyclic Dimensions and Standard RAG Colors.

The picture below shows the list of variables we used . They are self-explanatory.

Interactions

Switch between shapes

In order to switch between shapes we use the variable input component as button group and use a variable to store the current shape.

Variable used: vShape

Change the shape parameters

We use the slider input variable components to change the parameters of the shapes. We re-use the components between the waves (Sine and Cos) and the circle. You would have noticed that in the variables image above the circle variables are mapped to the wave variables. This is to re-use the components while making the formula more readable as you will see later in the blog.

So, based on the current shape the title and the min/ max values of the slider components change. The below image shows how we implemented it for the first component. We use the same approach for all the components.

Title

=if (vShape='Circle', 'Circle Center X','Frequency')

Min/ Max

Min

=if (vShape='Circle', -9,1)



Max

=if (vShape='Circle', 9,5)

With this we will be able to choose the shape to plot and also change the characteristics of the shape. Next we will see how to plot the shape in a graph.

Draw the shapes

We will use XY visualization to draw the shape. Before we visualize we will have to arrive at the equations for the shapes.

For the waves, the x-axis will be a time interval starting from zero. For this demonstration, i decided to choose 360 data points that i can use both for waves and the circle. I chose 360 because for circle i would use each 360 data points for the 360 degree but we can use more data points and all will be plotted in the same place for the circle. These data points are nothing but the count from 0 to 360.

Equations for Sine and Cos Waves

Following are the equations for Sine and Cos Waves

Sine wave: Amplitude * Sin ( 2 * pi * Frequency * Time Interval)

Cos wave: Amplitude * Cos (2 * pi * Frequency * Time Interval)

These equations are simpler ones that does not have phase shift and offset.

Now we have to figure out how we will increment Time Interval in the above equation so that we get a meaningful wave. The time interval has to be in such a way that we plot all the 360 points and cover the number of waves in a given time. Remember, that one of the parameters is the number of seconds to plot. So to compute the interval, we have the WaveTime variable. This variable is determined by the following formula

WaveTime = 1/ (WavePoints/Number of Seconds)

To keep it simple, following are the X and Y axis equations for the shapes and these equations are created using the variables that we defined earlier.

Sine Wave

X-Axis:  Counter * WaveTime  

Y-Axis:  Wave1Amplitude  * Sin( 2Pi() Wave1Frequency * Counter*WaveTime 

Cos Wave

X-Axis:  Counter * WaveTime 

Y-Axis:  Wave1Amplitude  * Cos( 2Pi() Wave1Frequency * Counter*WaveTime 

Circle:

X-Axis: Circle1Center_X + Circle1Radius*Cos(Counter)

Y-Axis: Circle1Center_X + Circle1Radius*Sin(Counter)

Now we combine all these equations and write the X axis and Y-Axis formula for the visualization. We can use either IF ELSE or PICK MATCH as shown below.

X Axis
=if (vShape =  'Circle',Circle1Center_X + Circle1Radius*Cos(Counter), Counter * WaveTime)

Y Axis
=   Pick( match(vShape, 'Sine','Cos','Circle'),
 Wave1Amplitude  * Sin( 2Pi() Wave1Frequency * Counter*WaveTime ),
   Wave1Amplitude  * Cos( 2Pi() Wave1Frequency * CounterWaveTime ),     Circle1Center_Y + Circle1RadiusSin(Counter))

Changing the Range of X and Y Axis

Earlier in this blog article, we saw that we changed the Min and Max values of the slide based on the shape. So in order to accommodate the same, we have to change the range of the X and Y axis of the chart too. It is achieved using the below formula.

Under Appearance/ Range, Choose custom and Min/Max and write the below formula

Min
=if(vShape='Circle',-10,-.1)

Max
=if (vShape='Circle',10,WaveSecondsToPlot+1)

Demo

The post Implement What-If Analysis in Qlik Sense appeared first on CodeWander.

Why Cyclic Dimension?

Imagine a scenario where your analysis is the same but the dimension that you analyze change. So instead of creating a new copy of the sheet you can reuse the sheet but change the dimensions of the charts in a single click.

Why not Alternate Dimension?

Alternate Dimension is the feature of Qlik Sense that comes closer to cyclic dimension. However, it does not give the advantage that cyclic dimensoin provides i.e. change the entire dashboard view.

How to enable a quick cyclic dimension?

Two main features that we will use

1. Vairabe input component from Qlik Dashboard Bundle
2. Variable

In this blog lets take the Odoo – Point of Sale qlik sense dashboard that I created as part of the Odoo Power BI Qlik sense with Talend series. This dashboard has a single sheet that looks like below.

The dashboard is very simple showing Sales metrics on the top and the following charts

1. Performance by hour of the day
2. Pie Chart – Sales share by product category
3. XY Chart – Performance by Products

For the demonstration purpose, lets say that we want the entire dashboard to be shown either by category or product. We want to introduce a second dimension in the performance by hour of the day which can either be product or category. Lets see the steps in achieving it.

Step 1: Create a variable

The first step is to create a new variable. In this example, I created a new variable called vView and assigned a value. The value must be the field that we want to use as the default dimension. Here I chose products field as my default dimension.

Step 2: Create a Variable Input Component

Drag and drop the variable input component and key in all the categories we want as part of the cyclic dimensions. I chose buttons with dynamic values as it gives option to choose a value and label. This will give a good tab like appearance with user friendly names.

In the dynamic values formula enter the following values to accomodate the product and category field names

='products.product_name~Product|products.product_category_name~Category'

Step 3: Replace the dimensions of the charts with variable name

We have created two basic blocks, namely the variable and the variable input component that will help us in changing the variable name. Now, we will use these variables in the charts that we want to change dynamically.

The dimension that i will use is as follows

=$(vView)

I made the following changes

1. Introduced a new dimension for the bar chart
2. Replaced the existing category dimension of pie chart
3. Replaced the existing product dimension of the XY Chart

Now the entire dashboard looks like below

Limitations: You cannot use this to cycle between two master dimensions. However you can create a master dimension using a variable and make the master dimension itself a cyclic dimension.

The post Qlik Sense Tips to Create a quick Cyclic Dimension appeared first on CodeWander.