Crosstable

Overview

Open the Data load editor and add the first load script below to a new tab.

The first load script contains a dataset to which the crosstable script prefix will be applied later, with the section applying crosstable commented out. This means that comment syntax was used to disable this section in the load script.

The second load script is the same as the first, but with the application of crosstable uncommented (enabled by removing the comment syntax). The scripts are shown this way to highlight the value of this scripting function in transforming data.

First load script (function not applied)

tmpData:
//Crosstable (MonthText, Sales)
Load * inline [
Product, Jan 2021, Feb 2021, Mar 2021, Apr 2021, May 2021, Jun 2021
A, 100, 98, 103, 63, 108, 82
B, 284, 279, 297, 305, 294, 292
C, 50, 53, 50, 54, 49, 51];
 
//Final:
//Load Product,
//Date(Date#(MonthText,'MMM YYYY'),'MMM YYYY') as Month,
//Sales

//Resident tmpData;

//Drop Table tmpData;

For more information about using inline loads, see Inline loads.

Results

Load the data and open a sheet. Create a new table and add these fields as dimensions:

• Product

• Jan 2021

• Feb 2021

• Mar 2021

• Apr 2021

• May 2021

• Jun 2021

This script allows the creation of a crosstable with one column for each month and one row per product. In its current format, this data is not easy to analyze. It would be much better to have all numbers in one field and all months in another, in a three-column table. The next section explains how to do this transformation to the crosstable.

Second load script (function applied)

Uncomment the script by removing the //. The load script should look like this:

tmpData:
Crosstable (MonthText, Sales)
Load * inline [
Product, Jan 2021, Feb 2021, Mar 2021, Apr 2021, May 2021, Jun 2021
A, 100, 98, 103, 63, 108, 82
B, 284, 279, 297, 305, 294, 292
C, 50, 53, 50, 54, 49, 51];
 
Final:
Load Product,
Date(Date#(MonthText,'MMM YYYY'),'MMM YYYY') as Month,
Sales

Resident tmpData;

Drop Table tmpData;

For more information about using inline loads, see Inline loads.

Results

Load the data and open a sheet. Create a new table and add these fields as dimensions:

• Product

• Month

• Sales

Once the script prefix has been applied, the crosstable is transformed into a straight table with one column for Month and another for Sales. This improves the readability of the data.

Overview

Open the Data load editor and add the load script below to a new tab.

The load script contains:

• A dataset which is loaded into a table named Targets.

• The crosstable load prefix, which transposes the pivoted sales person names into a field of its own, labeled Sales Person.

• The associated sales target data, which is structured into a field called Target.

Load script

SalesTargets:
CROSSTABLE([Sales Person],Target,1)
LOAD
*
INLINE [
Area, Lisa, James, Sharon
APAC, 1500, 1750, 1850
EMEA, 1350, 950, 2050
NA, 1800, 1200, 1350
];

Results

Load the data and open a sheet. Create a new table and add these fields as dimensions:

• Area

• Sales Person

Add this measure:

=Sum(Target)

If you want to replicate the display of data as the pivoted input table, you can create an equivalent pivot table in a sheet.

Do the following:

1. Copy and paste the table you have just created into the sheet.
2. Drag the Pivot table chart object on top of the newly created table copy. Select Convert.
3. Click Done editing.
4. Drag the Sales Person field from the vertical column shelf to the horizontal column shelf.

The following table shows the data in its initial table form, as it is displayed in Qlik Sense:

The equivalent pivot table looks similar to the following, with the column for each sales person's name being contained within the larger row for Sales Person:

Overview

Open the Data load editor and add the load script below to a new tab.

The load script contains:

• A dataset representing sales and targets data, organized by area and month of the year. This is loaded into a table called SalesAndTargets.

• The crosstable load prefix. This is used to unpivot the Month Year dimension into a dedicated field, as well as to transpose the matrix of sales and target amounts into a dedicated field called Amount.

• A conversion of the Month Year field from text to a proper date, using the text-to-date conversion function date#. This date-converted Month Year field is joined back onto the SalesAndTarget table via a Join load prefix.

Load script

SalesAndTargets:
CROSSTABLE(MonthYearAsText,Amount,2)
LOAD
	*
INLINE [
Area	Type	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22
APAC	Target	425	425	425	425	425	425	425	425	425	425	425	425
APAC	Actual	435	434	397	404	458	447	413	458	385	421	448	397
EMEA	Target	362.5	362.5	362.5	362.5	362.5	362.5	362.5	362.5	362.5	362.5	362.5	362.5
EMEA	Actual	363.5	359.5	337.5	361.5	341.5	337.5	379.5	352.5	327.5	337.5	360.5	334.5
NA	Target	375	375	375	375	375	375	375	375	375	375	375	375
NA	Actual	378	415	363	356	403	343	401	365	393	340	360	405
] (delimiter is '\t');

tmp:
LOAD DISTINCT MonthYearAsText,date#(MonthYearAsText,'MMM-YY') AS [Month Year]
RESIDENT SalesAndTargets;

JOIN (SalesAndTargets)
LOAD * RESIDENT tmp;

DROP TABLE tmp;
DROP FIELD MonthYearAsText;

Results

Load the data and open a sheet. Create a new table and add these fields as dimensions:

• Area

• Month Year

Create the following measure, with the label Actual:

=Sum({<Type={'Actual'}>} Amount)

Also create this measure, with the label Target:

=Sum({<Type={'Target'}>} Amount)

If you wish to replicate the display of data as the pivoted input table, you can create an equivalent pivot table in a sheet.

Do the following:

1. Copy and paste the table you have just created into the sheet.
2. Drag the Pivot table chart object on top of the newly created table copy. Select Convert.
3. Click Done editing.
4. Drag the Month Year field from the vertical column shelf to the horizontal column shelf.
5. Drag the Values item from the horizontal column shelf to the vertical column shelf.

The following table shows the data in its initial table form, as it is displayed in Qlik Sense:

The equivalent pivot table looks similar to the following, with the column for each individual month of the year being contained within the larger row for Month Year: