Hi qlik community members, 👋

Check out this solution to create your own gradient color palette with the ColorMix2() function.

Resolution in stages

Let's see how we can achieve this result, in a simple data model.

1 – Find the largest and smallest value of your measurement.

max(total aggr(Sum([Total value]),Customer))

min(total aggr(Sum([Total value]),Customer))

 2 – Calculate the difference between these values

max(total aggr(Sum([Total value]),Customer))
-
min(total aggr(Sum([Total value]),Customer))

3 – Now we will create a range, where we will identify positions 0 and 1 of the indices, or basically the values ​​from 0% to 100%.

(sum([Total amount])
-
min(total aggr(Sum([Total amount]),Customer)))

/

(max(total aggr(Sum([Total amount]),Customer))
-
min(total aggr(Sum([Total amount]),Customer)))

4 – To apply the gradient, in your graph go to the Presentation window -> Colors and Legend -> Colors Customize by expression. In the check box, leave the option selected as shown below:

5 – Finally, in the expression insert your calculation within the ColorMix2() function. 

ColorMix2((sum([Total amount])
      -
min(total aggr(Sum([Total amount]),Customer)))

/
(max(total aggr(Sum([Total amount]),Customer))
-
min(total aggr(Sum([Total amount]),Customer))),red(),blue(),yellow())

For more details see this and other color functions in the qlik help. 

https://help.qlik.com/en-US/sense/November2023/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/ColorFunctions/color-functions-charts.htm

I hope you like it! 🙂

Attached is the qvf for anyone who wants to see the example model exposed.

Yours sincerely, Matheus

The rolling months method is frequently utilized in business analytics and financial reporting to provide a more accurate representation of performance over time. This document demonstrates how to create a flag in the script to perform rolling N months analysis.

There are various ways to achieve rolling month analysis in graphs using the rangesum() function in conjunction with the above() function on the front end, along with set analysis. While front-end solutions work well for simpler measures and smaller datasets, they can lead to performance issues or increased complexity in cases of more intricate requirements.

Therefore, it is advisable to create a flag in the script using a master calendar.

This approach will establish a flag in the script for rolling months, which can then be used as a filter or in set analysis measures on the front end

// Load min and max Date from Fact

MaxDate:
LOAD num(max(FieldValue('Date', recno()))) as MaxDate,
     num(min(FieldValue('Date', recno()))) as MinDate
AUTOGENERATE FieldValueCount('Date');

let vMaxDate= Peek('MaxDate',0,'MaxDate');
let vMinDate= Peek('MinDate',0,'MaxDate');

drop table MaxDate;

// Generate Dates using min and max date
Cal:
LOAD *,
     MonthName(Date) as MonthYear;
LOAD date($(vMinDate)+IterNo()-1) as Date
AutoGenerate(1)
While $(vMinDate)+IterNo()-1<=$(vMaxDate);

MaxMonthYear:
LOAD num(max(FieldValue('MonthYear', recno()))) as MaxMonthYear
AUTOGENERATE FieldValueCount('MonthYear');

//  Variable used to restrict MonthYear to <=current month while looping

LET vMaxMonthYear = monthname(Peek('MaxMonthYear',0,'MaxMonthYear'));

// Define Rolling N in Inline table. 1 is the default value for current month

RollMonth:
LOAD * Inline [
RollMonth
1
2
3
6
12 ];

Calender:
LOAD * Inline [
junk ];

for i=1 to FieldValueCount('RollMonth')

LET vRollMonth= FieldValue('RollMonth',$(i));

Concatenate(Calender)
LOAD Date,
     MonthYear,
     Rolling_Months,
     month(Rolling_Months) as Month,
     Year(Rolling_Months) as Year,
     if(Flag='Rolling1','CurrentMonth',Flag) as Rolling_Flag
where Rolling_Months<=Date#('$(vMaxMonthYear)','MMM YYYY');

LOAD Date,
     MonthYear,
     monthname(MonthYear,IterNo()-1) as Rolling_Months,
     'Rolling'&$(vRollMonth) as Flag
Resident Cal
While IterNo()-1<=$(vRollMonth)-1 ;

NEXT

DROP Tables Cal,MaxMonthYear,RollMonth;
DROP Field junk;

Use Rolling_Months as your month selection or dimension in your report when performing month-based analysis. You can also utilize Rolling_Flag either as a filter or as a condition in set analysis to construct the required measure.

Please refer to the attached applications.

I 'd like to thank avinashelite‌ for sharing the method to load the multiple excel files with the multiple sheets. You can find the article at the link below.

Loading Multiple Excel Sheets Dynamically along with file name and sheet name

Unfortunately, this method has some limitations. It only works when all files have the same number of non-blank sheets, and all sheets must have the same number of columns with identical names.

I’d like to share a method below that works effectively without these restrictions

1) Define some variables to automate process.

/* Change the below variable with actual folder path */

LET vExcelFilePath = '..\Files'; // Root folder path on which all the excel files are stored. It might contain sub folders as well.
LET vFileExtension = 'xlsx'; 
LET vQVDFilePath='..\QVD'; // Path on which a QVD is stored which contains data from all the excel files
LET vQVDName ='Sales_data'; // Name of a physically stored QVD File 

2) Define function to get the list of all the files located inside the folder. It also scans sub folders inside the parent folder.

/* Below subroutine is created to get the lis of excel files stored in specific folder and sub folder within that */

sub ScanFolder(Root)

         for each vFile in filelist( Root & '\*.' & vFileExtension)

                FileList:
                LOAD '$(vFile)' as Files
                AutoGenerate 1;

         next vFile

                for each SubDirectory in dirlist( Root & '\*' )

                    call ScanFolder(SubDirectory)

                next SubDirectory

end sub

Call ScanFolder('$(vExcelFilePath)') ;

let vFile = Null();

3) Define a function to loop through the list of Excel files generated by the previous function. Let’s take a look at the connection string below. I’ve used CONNECT64 because I’m working with a 64-bit ODBC driver and Excel application. If you’re using 32-bit ODBC drivers and Excel, you may need to use CONNECT32 instead. The 'Excel Files' DSN is created on the machine using either 64-bit or 32-bit ODBC drivers. Typically, when Office drivers are installed, the 'Excel Files' DSN is created automatically, but in some cases, it may need to be created manually. If your DSN name differs, be sure to update it in the connection string below."

ODBC CONNECT64 TO [Excel Files;DBQ=$(vFile)];

/* Below subroutine is created to load all the excel file and store it into the QVD. */

SUB load_all_excel_files_and_store_in_qvd(LoadData)

  FOR EACH vFile IN FieldValueList('Files');

  ODBC CONNECT64 TO [Excel Files;DBQ=$(vFile)];

  Temp:
  LOAD *;
  SQLtables;
  DISCONNECT;

  [$(vTableName)]:
  LOAD * INLINE [
  junk ];

      FOR i = 0 TO NOOFROWS('Temp')-1

            LET vSheetName = PURGECHAR(PURGECHAR(PEEK('TABLE_NAME', i, 'Temp'), CHR(39)), CHR(36));

            Set ErrorMode=0; // Disable error mode to avoid error while loading blank sheet

            CONCATENATE([$(vTableName)])
            LOAD  *,
                  SubField('$(vFile)','\',-1) AS FileName,
                  '$(vSheetName)' AS Sheet_name
            FROM $(vFile)(ooxml, embedded labels, table is [$(vSheetName)]);

                 if len(FieldName(FieldNumber('A','Data'),'A'))>0 THEN

                      Drop Field A;  // When there is a blank sheet in the excel file, field A is created which we don't want

                 ENDIF
   
            Set ErrorMode=1;

      NEXT

  DROP TABLE Temp;
  DROP FIELD junk;

  NEXT 

let vFile = NULL();

    if NoOfRows('$(vTableName)')>0 THEN

        STORE [$(vTableName)] into $(vQVDFilePath)\$(vQVDName).qvd(qvd); // store into the QVD file

    ELSE

        TRACE "Data is not available";

    ENDIF

END SUB

CALL load_all_excel_files_and_store_in_qvd(LoadData);

DROP Table FileList;

Note: This Script is best suited for QlikView

Please feel free to offer any suggestions to improve this document.

Thanks & Regards,

Kushal Chawda

A calendar is very useful when you want to link your data to different time periods, e.g. when you want to display your KPIs over different years or months. Often you only have one date and you just want to use a standard calendar. For this case, there are plenty of resources on this community. You can find a good overview on How to use - Master-Calendar and Date-Values..

If you have several date fields, you should most likely have several calendars defined in your script. See

Why You sometimes should Load a Master Table several times

Canonical Date

If you want to use a non standard calendar, like a fiscal calendar or a 4-4-5 calendar, the challenge becomes more difficult. See e.g. Fiscal Yearor Recipe for a 4-4-5 Calendar‌.

The script posted here will help you create a more complicated calendar. It has parametrized examples for the following calendars:

• Standard Gregorian month-based calendar
• Standard Gregorian week-based calendar
• Standard Gregorian week-based calendar with broken weeks
  The first and last weeks of the year do not span the shift of the year and they do not necessarily have 7 days
• Month-based fiscal calendar
  The Gregorian months are used as fiscal months, but January is not necessarily the first month of the fiscal year.
• Week-based 4-4-4 calendar
  The fiscal year is divided into 13 months with 4 weeks each. Occasionally there is a 14th month with one week.
• Week-based 4-4-5 calendar
  The fiscal year is divided into 4 quarters, each with 3 months. The first month of the quarter has 4 weeks, the second has 4 weeks, and the last has 5 weeks. Occasionally the last month of the year has an additional week.
• Week-based 4-5-4 calendar
  The fiscal year is divided into 4 quarters, each with 3 months. The first month of the quarter has 4 weeks, the second has 5 weeks, and the last has 4 weeks. Occasionally the last month of the year has an additional week.
• Week-based 5-4-4 calendar
  The fiscal year is divided into 4 quarters, each with 3 months. The first month of the quarter has 5 weeks, the second has 4 weeks, and the last has 4 weeks. Occasionally the last month of the year has an additional week.
• Broadcast calendar
  The months are week-based and start on the first day of the week that contains the first day of the Gregorian month.

There are several parameters that you can use to configure your calendar: The first month of the year, the first day of the week and the first week of the week-based year.

The script is commented, so hopefully you can read it and understand it. You can paste it straight into an empty app and run it to evaluate how it works. If you want to use parts of it inside one of your own apps, you may need to rename some fields.

The script should work in both Qlik Sense and QlikView.

Good Luck!

HIC

Here are some example show conditions.  The second is if you have multiple different groups that shouldn’t have access to a given field.

=if(OMITGROUP=’SALARY’,0,-1)

=if(match(OMITGROUP,’SALARY’,’CONTACTINFO’,’ACCOUNTING’)>0, 0, 1)

Have you ever wondered how the examples from the Qlikview help may look like?

Please see below and enjoy responsibly...

Below() and Above()

Returns the value of expression evaluated with the chart's dimension values as they appear on the row above the current row within a column segment in a table or, in the case of bitmap charts, in the chart's straight table equivalent (Actually all QlikView charts have a straight table equivalent with the exception of the pivot table which has a more complex structure.).

On the first row of a column segment a NULL value will be returned, as there is no row above this one.

If the chart is one-dimensional or if the expression is preceded by the total qualifier, the current column segment is always equal to the entire column........

• Returns value from  previous (Above) ornext (Below) row(s)
• Returns NULL for the first or last row.
• Equivalent to before()and after()for columns in Pivot Table.

Syntax:

TOTAL[<fld{,fld}>]]expr - expression

offset - if greater that 0 will move the evaluation of expression to rows

further down or above

count - this parameter will only works with Chart Range Function (like RangeSum), it will specify the numbers of rows to be taken for calculations.(Please see last example)

Data Model:

(Copy and Pasted below code into Edit Script window and reload)

LOAD * inline

[

Year ,Month ,Sales

2015 ,January, 10

2015, February,20

2015 ,March ,30

2014 ,January ,10

2014 ,February,20

2014 ,March ,30

2013 ,January ,10

2013 ,February ,20

2013 ,March,30

]

Example 1:

Let's build a Straight Table with Year and Month as dimensions and expressions as below:

The left hand side shows use of sum(Sales) the right hand side result from our new expression.

                sum( Sales )    -----------> above(sum( Sales ) )

In each groups the first value is now NULL,the last values(30) have been omitted and the rest of the rows have been assigned value from one row below current row.

                sum( Sales )    ----------->  below(sum( Sales ) )

In each groups the last value is now NULL,the first values (10) in each group have been omitted and the rest of the rows have been assigned value from one row below current row.

Example 2

By specifying the second criteria as 2 ,values in each group are shifted two rows up or down

          sum( Sales )-----------> below sum( Sales ), 2 )

          sum( Sales )-----------> above( sum( Sales ), 2 )

Example 3

        above(TOTAL sum( Sales ) )      below(TOTAL sum( Sales ) )

Adding TOTAL before Sum will result with the first or last value to be omitted and the calculation to be shifted one row down or up.

Example 4

rangeavg (Above(sum(Sales),1,2))

rangeavg (Below(sum(Sales),1,2))

RangeAvg() takes 3 parameters

-expression ---> Above/Below(sum(Sales),

-offset of rows--->1

-number of rows to sum--->2

                                                   sum(Sales)                         rangeavg (Above(sum(Sales),1,2))

For each group in right table:

• First row is NULL
• Rows 2 and 3 are averages of values from two rows above current one (first we are going 1 row above and then adding 2 rows)

      sum(Sales)                          rangeavg (Below(sum(Sales),1,2))

For each group in right table:

• Rows 1 and 2 are averages of values from two rows below current one (first we are going 1 row above and then adding 2 rows)
• Last row is NULL

Still feeling hungry?

How to use - Only()

Do you Qualify?- How to use QUALIFY statement

How to use - Dimensionality()

Missing Manual - GetFieldSelections() + Bonus Example

MaxString &amp; MinString - How to + examples

The second dimension... or how to use secondarydimensionality()

Missing Manual - Below() and Above()

How to use- Dual()

Missing Manual - Before() and After()

How to use - MissingCount()

What it is used for?

The Qualify statement is used for switching on the qualification of field names, i.e. field names will get the table name as a prefix.’

In situation where we have two (or more) Tables with the same field names:

Product	Payment

The Qualify statement will assign name of the Table to fields:

Remember

• To use the script below the Excel file must be saved in the same folder that your qvd file

        Otherwise you will need to make changes the this path - [Qualify.xlsx]

QUALIFY *;
Product:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(ooxml, embedded labels, table is Product);

QUALIFY *;
Payment:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(ooxml, embedded labels, table is Payment);

The Outcome:

Table Viewer:

Read only if you need to know more....

If we have not used ‘QUALIFY’ statement in situation as above Qlikview would map both of the Tables with outcome like below:

The end result  -merge of those two Tables is correct only for the “Serial No” fields

The “Value” and “Category” fields although merged is none of use to us.

To fix this we can only Qualify fields that we do not want to associated:

QUALIFY Category,
Value;
Product:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(ooxml, embedded labels, table is Product);

QUALIFY Category,
Value;
Payment:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(ooxml, embedded labels, table is Payment);

or by using UNQUALIFY statement:

(which works as opposite to QUALIFY)

QUALIFY *;
UNQUALIFY [Serial No];
Product:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(ooxml, embedded labels, table is Product);

QUALIFY *;
UNQUALIFY [Serial No];

Payment:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(ooxml, embedded labels, table is Payment);

In second example the ‘QUALIFY *’ part will add Table name to all fields and the UNQUALIFY statement will remove those names only from specified fields(this method is very useful if we got large numbers of fields)

Outcome is as below:

Fields:

Table Viewer:

Feeling Qlikngry?

How To /Missing Manual(18 articles)

When showing data in a month on month style the current month can often have a large drop off.  This can be prevented by providing a run rate for the current month.  This is where the known values for the current period are extrapolated forward for the rest of the period, providing an estimate of where the period will end.  For example, if you have a value of 1000 on day 10 of a 30 day month, you could calculate a run rate of 3000 for the month.

This solution was created in response to a question on Qlik Community, which you can find here:

Re: Last time value in time line chart

I have documented how this document works and the reasons why you might use it in a blog post here:

https://www.quickintelligence.co.uk/qlik-run-rate/

There is also a link to the QlikView version of this application on the blog post, and further discussions on why run rates are good to have.

I hope that you find it useful.  You will find other applications that I have uploaded under my profile on QlikCommunity.

Steve

https://www.quickintelligence.co.uk/blog/

Often when building QlikView applications, or picking up applications which have been built by someone else I want to have a quick and easy way of viewing the data that is in that application.  To enable me to do this I have put together a page of objects that lists all tables and fields in the data model and then gives outline information about any selected field.  These objects can be copied and pasted into any QlikView application to view the data model of that document.

I have documented how this document works and the reasons why you might use it in a blog post here:

https://www.quickintelligence.co.uk/qlikview-data-profiler/

Please see the blog post for further details on using this document.

There is now a Qlik Sense version of this app, which you can find on Qlik Community here:

Qlik Sense App: Generic Data Profiler

I hope that you find it useful.  You will find other applications that I have uploaded under my profile on QlikCommunity, or on our Downloads Page.

Steve

https://www.quickintelligence.co.uk/blog/

‎

PLEASE NOTE: The 'With Mask' version of the file includes an experimental tab that may or may not work well on large data sets.  Please see comment below for details.  If you are not sure which to download go for DataProfiler.qvw.  Thanks!

This example QlikView document shows how to create a bar chart that shows variance to target, both as an absolute value and as a percentage.

The charts produced look like this:

By showing both these charts you can see both how regions are comparing to each other and against the targets that have been set for them.

The example was created to accompany the Quick Intelligence blog post, that can be read here:

https://www.quickintelligence.co.uk/qlik-target-bar-chart/

The example is also available on Qlik Community as a Qlik Sense application.

A list of all our downloadable example files can be found here:

https://www.quickintelligence.co.uk/examples/

Hope you find this application useful.

Regards,

Steve

https://www.quickintelligence.co.uk/

• What techniques are there to generate “new” tables; tables that don’t exist in the source data?
• How do I loop over one and the same record in source data?
• How do I propagate a value from above record to the current one?
• How do I populate a sparsely populated field?
• How do I generate all combinations – the Cartesian product – of two or more fields?
• How do I make simulations in QlikView?

These questions and others are answered in this Technical Brief.

HIC

There are many non-Gregorian calendars used in the world today. This page posts Qlik scripts for some of them.

The Julian calendar

The Julian calendar is easy to recreate in a master calendar in a Qlik app. All you need to do is to generate all days in a four-year cycle and assign the appropriate months and day numbers. The file JULN_Script.txt contains a script that does this.

The Hijri calendar

The Hijri calendar, or Islamic calendar, is a purely lunar calendar, containing 12 months based on the motion of the moon. This means that the Hijri year always is shorter than the tropical year, and therefore it shifts with respect to the Gregorian calendar.

To create a Hijri calendar, you need a table containing the month starts (HIJR_Calendar.txt). From this, you can generate the master calendar of your data model. Attached you will also find tables containing template month names and day names. All files for the Hijri calendar have the HIJR prefix.

This calendar, like any other Hijri calendar based on calculation, only gives an estimated date. The calendar is not based on the actual sighting of the moon, which is required for the beginning of some of the months. For a proper calendar you should contact your local Muslim scholar.

The Hebrew calendar

The Hebrew calendar is used today predominantly for Jewish religious observances. It is a lunisolar calendar with 12 months based on the motion of the moon. However, to prevent the year from shifting with respect to the seasons, a leap month is inserted approximately every third year.

To create a Hebrew calendar, you need a table containing the month starts (HEBR_Calendar.txt). From this, you can generate the master calendar of your data model. Attached you will also find tables containing template month names and day names. All files for the Hebrew calendar have the HEBR prefix.

The Shamsi calendar

The Shamsi calendar, also known as Persian calendar or the Jalaali Calendar, is a purely solar calendar, containing 12 months based on the zodiac constellations. The year always starts at the vernal equinox as seen from the Tehran horizon. This means that the Shamsi calendar never shifts with respect to the seasons.

To create a Shamsi calendar, you need a table containing the vernal equinoxes (EQNX_Equinoxes.txt). From this, you can generate the master calendar of your data model. Attached you will also find tables containing template month names and day names. All files for the Shamsi calendar have the SHMS prefix. You also need the file EQNX_Equinoxes.txt.

The French Republican calendar

The French Republican calendar, also called the French Revolutionary calendar, was the official calendar in France from 1793 to 1805. It is a purely solar calendar, containing 12 months with 30 days each, and an additional 5-6 days at the end of the year. In this version, the year always starts at the autumnal equinox as seen from the Paris horizon. This means that it never shifts with respect to the seasons.

To create a French Republican calendar, you need a table containing the autumnal equinoxes expressed as Gregorian dates (EQNX_Equinoxes.txt). From this, you can generate a French Republican master calendar for your data model. Attached you will also find tables containing month names and day names. All files for the French Republican calendar have the FREP prefix. You also need the file EQNX_Equinoxes.txt.

About the scripts

The scripts generate a number of fields, e.g. Year, Month, Day of month and full date. All fields are dual where applicable, so they are correctly sorted.

The algorithms used for these five calendars are not identical, but they are similar. The basic structure is:

1. Load the year starts (or month starts) as Gregorian dates from an external table. Or generate them from some deterministic rule.
2. Generate all Gregorian dates between one year start and the next.
3. Load non-Gregorian month names and other data from external tables, and join these onto the calendar using the appropriate key, e.g. non-Gregorian month number.

Script files are named XXXX_Script.txt. Each of the script files will run as it is if you include it in an empty app and place the other source files in the same folder as the app. To use the script in an existing app, you need to remove the mock-up fact table from the script, and maybe rename some of the fields.

The information in the source files have been compiled from different open sources. They may contain errors and QlikTech cannot assume any responsibility for such errors or what these errors may lead to when the data is used. Consider the files as templates for how to solve the challenge of implementing a non-Gregorian calendar. Use at your own risk and verify that the content meets the requirements that you have.

See also the corresponding blog post: Non-Gregorian calendars.

HIC

Hierarchies are an important part of all business intelligence solutions, used to describe dimensions that naturally contain different levels of granularity. Some are simple and intuitive whereas others are complex and demand a lot of thinking to be modeled correctly.

• What is the difference between a balanced and an unbalanced hierarchy?
• How can a hierarchy be loaded into QlikView?
• Which data model should be used?
• How do I create an authorization scheme using a hierarchy?
• How can I check for data integrity problems in the hierarchy data?

These questions and others are answered in this Technical Brief. Attached you will also find a sample unbalanced, n-level hierarchy along with the script to load it.

See also the following blog posts:

Unbalanced, n-level hierarchies

Authorization using a Hierarchy

HIC

2013-11-26: Added section on authorization.

One of the features that I miss from QlikView when working with Qlik Sense is the Cycle Group.  Allowing the user to pick a dimension and have that update in all charts where it was used is incredibly useful.

To get around this I use a data island listing dimensions and a filter box to pick which dimension is to be used.  This application demonstrates the technique, over some data from Grand Slam Tennis Tournaments.

There is a full description of how to create the application from the ground up, in this blog post:

https://www.quickintelligence.co.uk/qlik-sense-cycle-group/

I hope that you find this document useful.  You will find other applications that I have uploaded under my profile on Qlik Community, or listed on my website here.

Note that the document and blog post have a fixed dimension label, as this was all that was possible at the time of writing. The ability to have dynamic labels has since been added, and you can create a label on the Master Dimension with this code:

='$(=vDim)'

This will then show the field name as the column title and as the label on any charts where it is used.

Also, the addition of variable extensions in the Extensions Bundle means that you can use this to pick your field, rather than a filter pane - which looks a little neater. The filter pane does allow you to add extra fields in the load script (perhaps from a spreadsheet).

Steve

Update May 2022

The Wikipedia pages that this app loaded from were updated, so I have uploaded a revised app which now loads from the new layout. It also now contains stats from the Ladies game.

If you want a even more dynamic user interface, with users selecting both dimensions and measures, then please take a look at my tutorial post on the Instant Sense Application, here:
https://www.quickintelligence.co.uk/instant-qlik-sense-application/

You can try the application for yourself here:
https://www.quickintelligence.co.uk/sense/gs-app.html

Or download it from here:
https://www.quickintelligence.co.uk/instant-qlik-sense-app/

Sometimes it's convenient to kick off a geospatial operation in runtime. Since all Qlik GeoAnalytics connector operations are performed at load time the operations are not available as an expression. One way to overcome this limitation is to use on demand app generation (ODAG).

• This small example shows how to kick off travel area calculations for 10 capitals on demand.
• The ODAG start app is used to limit the selection to 10 cities and launch the app with the result.
• The selection is passed from the start app to the template app using ODAG bindings, see load script and documentation for more details.
• The ODAG template app is used for defining the calculations and the look of the result app.

The examples is only possible to run in Qlik Sense Enterprise Server with ODAG turned on.

Qlik GeoAnalytics is required.

Upload the apps to the server with QMC.

Update

Added example with the new simplified ODAG scripting and Dynamic views, see attachment "SelectDV.zip".

Dynamic Views makes it possible to embed a master visualization from the ODAG template in the main application, so the user don't have to spawn a new app.

Bullet points:

• MaxString and MinString belongs to Qlikview String Aggregation function.
• They are equivalent to MAX and MIN for integers
• They return the highest and lowest string value

What actually a value of a string?

This is the value described by ANSI character standard where 0 ( NULL) is equal  to 0 and ÿ to 255.

(For full ANSI character set please attached dosument)

Taking only the standard English alphabet (A-z)  ‘A’ will be the MIN and ‘z’ will be the MAX.

Remember:

In ANSI standard A < a and Z < z

(A=65,Z=90,a=97,z=122)

Language specific characters will be not recognized in the place where they are in your alphabet but they will be 'pushed' to the end

(see ANSI table)

MinString and MaxString works on dimensions (columns) only so you cannot use is to evaluate single character or strings .

(MaxString(‘a’,’z’…) is not supported

Examples:

1)    MIN & MAX (textbox)

Data	Expression	Results
	MinString(Category) MaxString(Category)

2) MIN and MAX of String per group (Straight table)

Data	Expression	Result
	MinString(Category) MaxString(Category)

3) With condition:

Return Max and Min string based on another column

We are looking for MIN and MAX string value from column Category where Country='Poland'

Data	Expression	Solution	Result
	MinString( if(Country='Poland', Category, null()) ) MaxString( if(Country='Poland', Category, null()) )

4) With LEFT/RIGHT

When used with LEFT the result will be the same as without as the value will be still based on first characters.

Data	Expression	Solution	Result
	=MinString( Right(Country,1)) =MaxString( Right(Country,1))

5) With MID

6) With Substring

When our data are little scrambled -TextBetween will return string between '.' and end of the row.

Data	Expression	Result
	=MinString(TextBetween(Country,'.','',1)) =MaxString(TextBetween(Country,'.','',1))

7) In set analysis

When Criteria of Sales is 2 return Country with highest and lowest string Value

😎 With Aggr()

For each Country return lowest and highest Category string value.

If know about other ways of using these functions please let me know.

Have you ever wondered how the examples from the Qlikview help may look like?

Please see below and enjoy responsibly...

Before () and After()

Description

Returns the value of expression evaluated with a pivot table's dimension values as they appear in the column before the current column within a row segment in the pivot table. This function returns NULL in all chart types except pivot tables.

On the first column of a row segment a NULL value will be returned, as there is no column before this one.

If the table is one-dimensional or if the expression is preceded by the total qualifier, the current row segment is always equal to the entire row.

If the pivot table has multiple horizontal dimensions, the current row segment will include only columns with the same values as the current column in all dimension rows except for the row showing the last horizontal dimension of the inter field sort order. The inter field sort order for horizontal dimensions in pivot tables is defined simply by the order of the dimensions from top to bottom.

Specifying an offset greater than 1 lets you move the evaluation of expression to columns further to the left of the current column. A negative offset number will actually make the before function equivalent to a after function with the corresponding positive offset number. Specifying an offset of 0 will evaluate the expression on the current column. Recursive calls will return NULL.

By specifying a third parameter n greater than 1, the function will return not one but a range of n values, one for each of n table columns counting to the left from the original cell. In this form, the function can be used as an argument to any of the special Chart Range Functions.

• Returns value from preceding (Before)or following (After) column
• Returns values only in Pivot Table otherwise NULL.
• Works only with Horizontal dimension.
• Equivalent to below() and above() for vertical Pivot Table dimensions.

Data Model:

(Copy and Pasted below code into Edit Script window and reload)

LOAD * inline

[

Product ,Category ,Sales

Salt ,White, 30

Salt, Brown ,40

Salt ,Red ,30

Sugar ,White ,10

Sugar ,Brown ,20

Sugar ,Red ,20

Wine ,White ,40

Wine ,Brown ,30

Wine ,Red ,10

]

Example 1:

Let's build a Pivot Tables with Product as dimension and expressions as below:

The left hand side shows an ordinary use of Sales the right hand side result from our new expression.

                sum( Sales )    ----------->  before(sum( Sales ) )

Salt value has been assigned to Sugar,

Sugar to Wine,

Salt  is now NULL .(there is no column before this one)

                sum( Sales )    -----------> after(sum( Sales ) )

Here we see the opposite result:

Sugar value has been assigned to Salt,

Wine to Sugar

and Wine is  NULL (no column after this one)

Example 2

Specifying the second criteria as 2 we shifted all values by 2 columns right or left.

                                before( sum( Sales ), 2 )

                                after( sum( Sales ), 2 )

Example 3

before(total sum( Sales ) )

after(total sum( Sales ) )

When using with one dimension this expression returns values as if we have used ordinary  before/after (Sum(Sales))

For more than one dimension the return value will be based on last column of the first(top) dimension and then appropriate columns for the second dimension.

Salt and Sugar have been omitted leaving Wine as the last column

Example 4

rangesum (Before(sum(Sales),1,2))

RangeSum() takes 3 parameters

-expression ---> Before/After(sum(Sales),

-offset of columns--->1

-number of columns to sum--->2

                                                                  sum(Sales)                            rangesum (Before(sum(Sales),1,2))

In our example we are shifting our calculation one column to the right:

-Salt -  As there is no column before Salt this column has been ignored

-Sugar -we are shifting our calculation one column to the left (Salt)  and are summing previous two columns.

As there is no column before Salt the final result is value of Salt alone.

-Wine- by shifting calculations one column to the left(Sugar) and summing two previous columns we getting 100+50=150

                    sum(Sales)                            rangesum (after(sum(Sales),1,2)

In this example we are shifting our calculation one column to the right:

-Wine -  As there is no column after Salt this column has been ignored

-Sugar -we are shifting our calculation one column to the right(Wine) and aresumming previous two columns.

As there is no column after Wine the final result is value of Wine alone.

-Salt- by shifting calculations one column to the right(Sugar) and summing two previous columns we getting 50+80=130

Still feeling hungry?

How to use - Only()

Do you Qualify?- How to use QUALIFY statement

How to use - Dimensionality()

Missing Manual - GetFieldSelections() + Bonus Example

MaxString &amp; MinString - How to + examples

The second dimension... or how to use secondarydimensionality()

Dear all,

Sharing simple example of What if analysis in Qlik Sense using Slider extension

GitHub - mhamano/qlik-sense-slider: Qlik Sense extension for a slider object‌

Hope will be good start for beginners

Vikas