How To / Missing Manual

A question that gets asked regularly is how to calculate a rolling measure over a period of N-months (or weeks or days). For example a 12-month rolling total or a 4-week rolling average. There are several ways to do this. But these approaches have some limitations that need to be kept in mind. I'll try to explain these approaches and some of their limitations.

• Accumulation
• RangeXXX functions
• Set analysis
• AsOf tables

First let's load some sample data. The SalesData table below will contain sales amount values for twelve consecutive months.

SalesData:
load * inline [
Month, Amount
1,6
2,4
3,7
4,3
5,4
6,9
7,5
8,7
9,8
10,6
11,9
12,7
];

This is a very simple table with little data, but this enough for demonstration purposes.

Once this data is loaded it's possible to create a straight table chart object to display the amount per month and a running total. As expected Month is used as dimension. The expression sum(Amount) will display the amount per month. Now let's add an expression to calculate a running total over three month periods.

This can be done in two ways. The first uses the Accumulation option for expressions. The same expression sum(Amount) is used, but now the Accumulation option is set to 3 Steps Back:

The second option uses the rangesum function. That expression looks like this:

rangesum(above(sum(Amount),0,3))

This sums the Amount value on current row and on the previous two rows. The resulting straight table looks like this:

This looks good. The rolling 3 months amount is calculated correctly. But what happens if a selection of months is made?

The rolling 3 month amount for month 4 is now 3 instead of 14. This is because month 1,2 and 3 are no longer included in the calculation for the rolling 3 month total.

The accumulation option has another issue. It only works when only one dimension is used in the straight table. The rangesum expression can be modified so it can calculate across dimension borders, but the accumulation option can't. The modified rangesum expression adds the total keyword to the above() function:

rangesum(above(total sum(Amount),0,3))

This goes some way to doing what we want, but the issue of displaying the wrong rolling 3 month amount for month 4 isn't solved yet. Contrary to what I first thought there is a solution for this, as Henric pointed out to me in the comments below. By combining the rangesum with the aggr function it's possible to calculate the correct rolling 3 month amounts for each month. The expression needed for that looks like this:

sum(aggr(rangesum(above(total sum({<Month=>}Amount),0,3)),Month))

Read Elif's blog post Accumulative Sums for a more complete explanation.

How about set analysis expressions?

This expression should calculate the sum of amount for the three month period:

sum({<Month={'>=$(=only(Month)-2)<=$(=only(Month))'}>}Amount)

But notice the only() function. This requires that only one month value is selected. After selecting month 4 the result looks like this:

This shows the selected month, but also the two previous months. And the values are not accumulated.

Ok, but what about the max function instead of only?

sum({<Month={'>=$(=max(Month)-2)<=$(=max(Month))'}>}Amount)

That gives a different result, but still not what we're looking for:

Now only the last three months are shown and again the values are not accumulated.

The 'problem' is that the set is calculated once for the entire chart, not per row. This means that it's not possible here to use Month both as a dimension and in the set modifier in the expression.

There's still an option left to discuss: AsOf tables.

The AsOf table links a period with all the periods in the rolling period. In this example months are used, but it can be applied to any type of period like hours, days or weeks.

For the three month periods needed for a rolling 3 month total this means a month should be linked to itself, the previous month and the month before the previous month. The only exceptions are the first month, which is itself the rolling 3 month period, and the second month that together with the first month is its rolling 3 month period. There are no months before the first month so the first two months cannot run over 3 months.

The AsOf table needed for the rolling 3 month calculations looks like this:

This table can be created like this:

AsOfMonth:
load
Month as Month_AsOf,
Month + 1 - IterNo() as Month
Resident SalesData
while IterNo() <= 3;

right join load Month Resident SalesData;

What this does is create three records for every month using the while statement. But that also creates three records for month 1 and 2. This would create a month 0 and a month -1. The right join is used to remove those incorrect month values.

Now that the AsOfMonth table is created the Month_AsOf field can be used instead of the Month field in the straight table. The expression for the straigh table is simply sum(Amount).

The straight table now shows the correct rolling 3 month total for month 4.

This can be expanded a little so not only the rolling 3 month can be shown, but also the amount for the month itself. To achieve this the AsOf table is modified by adding a field to label the type of period. And records are added to the table so each Month_AsOf value is linked to the matching Month value:

AsOfMonth:
load 'Current' as Type,
Month as Month_AsOf,
Month as Month
Resident SalesData;

Concatenate (AsOfMonth)
load 'Rolling 3' as Type,
Month as Month_AsOf,
Month + 1 - IterNo() as Month
Resident SalesData
while IterNo() <= 3;

right join load Month Resident SalesData;

There are now two types of periods available: Current and Rolling 3. Additional period types can be added for example for Rolling 6, Rolling 12 month and Year-to-Date periods. You can find examples of these types in the attached AsOf Table Examples.qvw document.

The period type can be used in the chart expressions to calculate the amount for the wanted period:

Current amount: sum({<Type={'Current'}>}Amount)

Rolling 3 month amount: sum({<Type={'Rolling 3'}>}Amount)

Concluding, there are two solutions that do what we want:

1. The rangesum-aggr combination

2. The AsOf table

The first has the advantage that no changes to the data model are needed. It's also possible to dynamically change the period to aggregate over by using a variable instead of a hardcoded number of periods. A disadvantage is that that it's a somewhat complicated expression that also comes with a performance cost.

The AsOf needs changes in the data model to create the AsOf table and fill it with the necessary records. The advantage is that it likely performs better on large data sets. It's also quite versatile since you can add several sets of records to meet different scenario's. The expressions you end up with in the charts are also less complicated. That said, it will likely take you some time to fully understand the AsOf table concept and realize all the places where you can put it to good use.

In the end you'll have to decide for yourself which solution is appropriate in your situation. With regards to the performance of one or the other solution, you will simply have to test to discover if the performance is acceptable. But of course such testing is already part of your development process, right?

I'd like to thank John Witherspoon for introducing me to the AsOf tables concept and Henric for pointing out the solution using the rangesum function in combination with the aggr function.

For those that find this document in a search - you may also want to check out these videos to learn Set Analysis:

A Beginners' Introduction to Set Analysis

About the attached document:

The doc is organized by question:

- to select all or just known members

- to select through search strings, variables that can store just members but also the whole set

- to select using two fields, a boolean test, a function like concat(), sum(), p() or e(), rank()

In this updated version, I have added some few sections and some examples.

It is a translation of a french doc I have written few weeks ago: http://community.qlik.com/docs/DOC-4889

Hello.

As far as I was designing apps for QV - I've found a great need in some charts to ignore all possible selections except some specific fields.

Exploring possible ways to do so - the first my decision was to use dollar-sign expression inside my Set Analysis which could make an exception like next:
(Let's imagine that in my scheme there are fields [Stock], [Manager], [Date], [City], [Client] and [Amount])

sum({1<[Stock]={'Stock 1','Stock 3'}>} Amount)

If I selected only 'Stock 1' and 'Stock 3' in field [Stock] - and this way has shown it's profit - you can get it using this expression:

sum({1<[Stock]=$(=if(GetSelectedCount([Stock], true( ))>0,'{' & chr(39) & GetFieldSelections([Stock],chr(39) & ',' & chr(39),GetSelectedCount([Stock], true( ))) & chr(39) & '}', ''))>} Amount)

If you need two, three, or more fields - you can simply add such code for every field into Set Analysis:

sum({1<[Stock]=$(=if(GetSelectedCount([Stock], true( ))>0,'{' & chr(39) & GetFieldSelections([Stock],chr(39) & ',' & chr(39),GetSelectedCount([Stock], true( ))) & chr(39) & '}', '')),[Manager]=$(=if(GetSelectedCount([Manager], true( ))>0,'{' & chr(39) & GetFieldSelections([Manager],chr(39) & ',' & chr(39),GetSelectedCount([Manager], true( ))) & chr(39) & '}', ''))>} Amount)

But what if you have a field containing hundreds of records? Can you imagine what will be the length of your expression? That's why I started to discover another way to do that. I've thought - what if I use not "1" Set, but "$" Set in my expression and write in it all the fields that must be ignored, something like that:

sum({$<[Date]=,[City]=,[Client]=,[Amount]=>} Amount)

So, using such expression - I get that all the fields except for [Stock] and [Manager] are ignored, but how car I make it automatic? Sure using our lovely dollar-sign expressions and system field $Field:

sum({$<[$(=Concat({1<$Field-={'Stock','Manager'}>}distinct $Field,']=,[')&']=')>} Amount)

where inside the set analysis of Concat function you can insert fields that you are interested in (by the way - there you can insert even tables using system field $Table and others).

• What techniques are there to match a number with an interval?
• How does the IntervalMatch prefix work?
• Should I join the interval match table with the event table?
• How do I generate an interval from a single change date?
• What is a Slowly Changing Dimension?
• How do I model a multi-level Slowly Changing Dimension?

These questions and others are answered in this Technical Brief.

Thank you Barry for good discussions.

See also the following blog posts

IntervalMatch

Creating a Date Interval from a Single Date

Creating Reference Dates for Intervals

Slowly Changing Dimensions

HIC

How could I make sure that certain data are only for certain users available?

The answer is SECTION ACCESS. Section access is a connection between an authorization table (commonly placed in a hidden script and/or outsourced in an include-variable) and the application data (most to a dimension table).

Further you need to enable section access within the document properties in tab open and especially to enable the strict exclusion is important then without this is section access more a comfortable usability function to avoid confusing by the users which needs only a small data-area from the application but not more suitable for real confidential data.

Introduction to Section Access

A Primer on Section Access

Data Reduction – Yes, but How?

Section Access: Strict Exclusion

QlikView Section Access Examples

Data Reduction Using Multiple Fields

Section Access (Data Reduction) using Active Directory

In addition to these there are more complex restrictions possible but before you build one think on alternatives like Document chaining whereby those applications then have a specialized and simpler section access.

Basics for complex authorization

Authorization using a Hierarchy

Restrictions based on section access could be applied on sheets and objects.

Sheets Security with Section Access

Sheets Security with Section Access File

Chart Level Access by user

Sheet level access

Sometimes there is a need to mask or de-identify data for certain users, for this see: Mask or de-identify data for certain users using Section Access.

At least the most important remark: before thinking on section access makes one or more BACKUP's from your applications then there aren't any go backs by mistakes! One exception (which don't nullified the suggestion of having Backup's) could you find here: Bypassing QlikView Section Access – Infinity Insight Blog.

There is some content-overlapping within the above used links but I think the complement to each other is useful and of course you will find many more interesting postings here within the qlik community to these topic - the notes here are a good starting point to go further.

Have fun!

Marcus Sommer

Year, Quarter, Month and Week To Date are the common analysis that I seen many applications. I will share the expression to do here

First to do this your data model should have the DateField in number format by applying floor

Similar to this

Floor(DateField) AS DateNum //it will gives you one whole number to represent date

YTD - Year To Date

A date should be selected and it will look for the Starting date of the year to the selected date.

Ex: date selected is 21-03-2014 then YTD is 01-01-2014 to 21-03-2014

Expression would be

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

QTD- Quarter to Date

In the place of year use Quarter

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(QuarterStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

MTD- Month to Date

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(MonthStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

WTD- Week to Date

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(WeekStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

if you want you can set a variable value as 'Year', 'Month', 'Quarter', 'Week', lets say vToDate and go with single chart and single expression

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num($(=vToDate& 'Start(Max(DateNum))')))<=$(=Max(DateNum))"}>} Sales)

Will keep your expression simple

Regards,

Celambarasan

At Qonnections 2019 I hosted a session called "273367 Qlik Sense Visualizations, best practice and top tricks". Here's the app I used with all tips including test data. Tip titles, more details in app:

* Chart and UI *
Butterfly chart
Gantt chart
Grid chart
Dynamic charts
Map image background
Previous/next in selection
Cyclic groups
Dividers
Date search filters
Dynamic bookmark
Select null

* Add ons *
Above and below
Color above average
Bell curves
Trendlines
Accumulation
Top 5 lines
Offset measures

* Table *
Custom report
Change null color
Sum with null
Subtotals and empty rows
Indentation
Indicators and symbols
Traffic lights
Pajama striping
Sparklines
Link to app

There is also video available: https://www.youtube.com/watch?v=fIOcb_JGNb4&t=3s

I want to emphasize that many of the tips are invented by others than me, I tried to credit the original author at all places when possible. Many of the tips have been published before on the Qlik Community, the app below can be viewed as my current top picks.

If you liked it, here's more tips in the same style:

• Sense Visualization tips part VI
• Sense Visualization tips 2021 part V QlikWorld
• Sense Visualization tips 2020 part IV
• Sense Visualization tips 2020 Part III Qlik World
• Sense Visualization tips 2019 Part II

Similar but for Qlik GeoAnalytics :

• Qlik GeoAnalytics tips 2020 QlikWorld
• Qlik GeoAnalytics tips 2019 Qonnections
• Qlik GeoAnalytics tips 2018 Qonnections

Thanks,
Patric

Have you ever created a P&L statement in QlikView?  Well, this technical brief outlines how to create a P&L statement in QlikView 11 using IntervalMatch.

Also check out this blog post.

Thanks,

Jennell

Generic Keys is a way to define keys between tables in a more general way so that their values can represent other things than individual key values; they can represent groups of key values or any key value. As an example, you can combine product IDs, product group IDs and a symbol for all products into one key field.

You can use generic keys to solve many data modeling problems:

• Authorization table with OR-logic between fields
  If you have an authorization table you sometimes want to have a slightly more complex access restriction than a simple logical AND between fields. It could be e.g., that a user is allowed to see sales for all regions for a specific product and at the same time the European sales for all products. Generic keys can be used here.
• Mixed dimensional granularity in a single fact table
  Often you want to compare actual numbers with budget numbers. The standard method is to concatenate these two tables into one common fact table. However, this new fact table could have mixed granularity in many of the dimensions. Generic keys can be used here.
• Multiple fact tables linked using a master link table
  Sometimes you have fact tables that are so different that you don’t want to concatenate them. To solve this problem you can make a data model that has a central link table and uses generic keys.

See more in the attached files.

HIC

PS I have been asked to make an example on comparing budget and actual numbers, so here it comes as a second attachment. It is a zip file with a mock-up order database with a budget. Create a new empty qvw; expand the zipped files in the same folder as the qvw; insert one of the two included script files into the qvw and run the script.

This video gets right to the point and shows you how to get started with Set Analysis. There are various blogs and articles on this topic that go in deeper, but I suggest you start here first.

Also consider this post by hic‌ Natural Sets

Next Video: Set Analysis - Part 2 - Cool Stuff You Can Do (video)

Dimensions and calculations over periods are essential parts from nearly each reporting. The analysis from data regarding to periods is quite independent from the to analyse data-area regardless if this are sales-, finance-, human-ressources- or production-data. Nearly nothing is more exciting within an analysis as the development from data over the time and the questions which are following like: Was these development expected or not and what could be the reasons?

However the handling from time-data could be difficult whereas the most could be avoided with a few simple rules.

The easiest way is often to use a master-calendar as dimension-table which is linked to the fact-table(s). Why and how, see:

Use of Master Calendar

The Master Calendar

The Master Time Table

The Fastest Dynamic Calendar Script (Ever)

Master Calendar with movable holidays

In more complex data-models is it often necessary to create several calendars and/or to use calendars which are divergent to normal year-calendars.

Canonical Date

Why You sometimes should Load a Master Table several times

Fiscal Year

Fiscal Calendar with Non-Standard Days (Not 1-31)

4-5-4 Master Calendar‌

Non-Gregorian calendars

Redefining the Week Start

Redefining the Week Numbers

Calendars

Important is to define and formate the time-dimension properly. Properly meant that the dimensions are (also) numeric then only numeric values could be calculated respectively compared with each other.

Background is that the date of 12/31/1899 is equal to 1 and each further day will be added by one so that the date of 12/31/1999 corresponds to 36525. Hours/Minutes/Seconds are fractions from 1, for example 1 / 24 / 60 = 0,000694 is equal to 1 minute.

This meant that all fields which should be calculated (comparing is calculation, too) should be (additionally) available as numeric field or as Dual-Field:

Get the Dates Right

The Date Function

Why don’t my dates work?

Often are additionally relative and/or continuing time-dimensions and flags very helpful to avoid complex calculations:

Relative Calendar Fields

Creating Reference Dates for Intervals

Calendar with period flags

Calendar with flags making set analysis so very simple‌

Period Presets: Compare Periods on the fly

The As-Of Table

Subroutine to Create Data Model for From/To Date Selection‌

Calendar with AsOf Flags, Compare Easter to Easter

Beside simple but frequent time-comparing with one or several time-dimensions in one object and simple expressions like sum(value) or count(order) are there more complicated questions like:

Year-over-Year Comparisons

Previous YTQ, QTD, MTD and WTD

Calculating rolling n-period totals, averages or other aggregations

Beside the above used links you will find many interessting postings here within the qlik community to these topic - the notes here are a good starting point to go further.

Have fun!

Marcus Sommer

ps: within the attachment is a german translation - deutsche Fassung.

If you have never thought about integrating predictive analytics and QlikView, may this article pique your interest on the topic.  If you have thought about the potential, but do not know how to get started, may this be the motivation to start your journey.

This demo example illustrates the art of the possible around the interoperability of the R statistics engine and QlikView 11 for enabling predictive algorithms within QlikView today.

R (http://www.r-project.org/) is a free software environment for statistical computing and graphics, which supports predictive modeling and scoring.  The example also introduces Rattle (Rattle: A Graphical User Interface for Data Mining using R -- http://rattle.togaware.com/).  Rattle was used to create the predictive model and generate the R code implemented within the example QlikView application.  The use of Rattle is not detailed here.  It is left up to you to explore.

The example assumes you understand R and how to create predictive models in R and how scoring works in R.  If you understand how to do those things in R and you know how QlikView works, this example should give you an idea of how:

1. you could implement a compelling QlikView application for discovering trends in data as you have become used to with QlikView
2. while adding the ability to predict outcomes using a proven, trained scoring model you have built in R
3. and while showing actionable predicted outcomes at the point of decision

-- all without the end user having to leave QlikView.

As in the previous example posted by Elif Tutuk, "Integrating QV with R example kit.zip" (http://community.qlik.com/docs/DOC-2975), the example utilizes statconnDCOM.  A slightly different approach in this example overcomes a break in functionality introduced into that earlier example by the changing in the behavior of one of the COM API methods in the most recent releases of statconnDCOM.

The integration is done using QlikView's built-in VBScript support to interoperate with the DCOM-based API.  This is just one way to integrate with R.  With a little research you can discover other potential opportunities for R integration (e.g., OpenCPU).  Also, note that R is just one such example of a statistical engine being integrated with QlikView.  Other solutions, such as commercially available statistical engines or predictive scoring engines may also be integrated in similar ways.

To get started with this example, save the attached file, QlikView-R_Predictive_Demo.zip, to your system.  Open the zip file, and copy the enclosed folder (QlikView-R_Predictive_Demo) to a location of your choice.  The location is not important as the example is portable and has no dependency on being placed in a particular folder on your system. Now navigate into the folder you just extracted and find the document named “Qlikview-R Integration Demo 20170310.pdf”.  Open the document and follow the instructions step-by-step.

UPDATE (May 7, 2014):  I updated the installation document (Qlikview-R Integration Demo 20140421.pdf) to eliminate the RAndFriends installer as it has been unavailable for some time.  This edition of the document references R 3.1.0.  Hopefully the instructions will continue to be a good guide for subsequent versions as well.  I will continue to monitor for changes in R, statconnDCOM and other R components which could effect how the demo application works going forward.  The example QlikView application itself remains unchanged. It is a Personal Edition version, so you should be able to explore it with a licensed QlikView Desktop or the Personal Edition.  Thank you for your continued feedback on this demo.

UPDATE (September 21, 2015):  Everyone who has been seeing the message "The license that is embedded in this document is not valid":  I attached an updated version of QlikView-R_Predictive_Demo.zip‌.  Ensure you overwrite the old QVW with the new one in this package.  That is the only change.  Please forgive this looooong overdue update. 

UPDATE (October 9, 2016):  Updated the embedded license to work with QlikView Desktop Personal Edition.

UPDATE (February 2, 2017):  Updated the embedded license to work with QlikView Desktop Personal Edition.

UPDATE (March 10. 2017):  A dependency for this particular example is statconnDCOM from the team at autstat (http://www.autstat.com/).  If you wish to utilize statconnDCOM, you will need to contact autstat directly and purchase a license or request an evaluation license for testing.

Hi all,

Mini Charts have always been a good feature in QlikView and It's really 'frustrating' that this has not been added (yet) in Qlik Sense.

With hopes that the Qlik Team will think about such improvements for the next releases, let's discuss here how to create our own mini chart in a Qlik Sense Table.

The following expressions might seem 'complicated' at first sight, but remember that It will be generic.

You'll only have to change your : Dimension(s) and Measure to adapt to your need.

Thus, their use will be very easy.

Let's begin :

Suppose we have:

As a dimension: OICA Region

As measures:

Sum(Sales)

Sum("Commercial Vehicle Sales")

Sum(Sales) / (Sum(Sales)+Sum("Commercial Vehicle Sales"))

Here is our current table:

Now, we want to add, a mini 'bar' to represent the %:

How to do so:

As an expression:

=repeat('█',

                  rangemax(rangemin( ceil(((column(1)/column(2))-1)*2.5),10),0)

                  )

With that, we repeat the square sign and precise the limits.

And as a text Color expression:

if( (sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales])))>=0.8,Green(),

if((sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales])))>=0.7,Yellow(),

Red()))

Result:

We can also Add the % in front of the bar by adding it in the expression:

From :

=repeat('█',

                  rangemax(rangemin( ceil(((column(1)/column(2))-1)*2.5),10),0)

                  )

To:

=repeat('█',

                  rangemax(rangemin( ceil(((column(1)/column(2))-1)*2.5),10),0)

                  ) & num((sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales]))),'# ##0,00%')

Result:

You can even alter it as follow:

repeat(chr(09608),round( sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales])) * 10))

&repeat(chr(09617),10-round( sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales])) *10))

&num((sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales]))),'# ##0,00%')

&if(sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales]))>0.7,'▲','▼')

result:

Now, let's suppose we want to add another mini chart, a trend one, to see the % expression by Year:

If it has increased or decreased from a year to the next one.

How to do so?

Expression:

=concat(

        aggr(

            if((sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales]))) > above((sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales])))),

              '▀',if((sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales]))) < above((sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales])))),'▄',' ')

            )

            ,[OICA region], (Year,(NUMERIC, ASCENDING))

        )

,'',[OICA region])

Wirh:

Red: Our Dimension

Green: The trending dimension

Blue: our expression compared to the previous one (by year, the trending expression)

If expression by Year > expression Previous Year then , Square Up

If expression by Year < expression Previous Year then , Square down

Result:

Special thanks to blog Von Heldendaten in which I found this:

QlikView + Qlik Sense Blog von Heldendaten: Qlik Sense Calendar Measures & "Minicharts" in Tabellen.

Hope you like it !

Omar,

Qlik Sense vs Power BI & Tableau.

Hi Techies,

Qlik Sense QMC has great feature of QMC resource security where resource is like stream, application and master items(sheets, field, bookmarks, story etc) could be hidden from user or group of users. This document will guide you how to implement resource security in Qlik Sense from QMC to the deepest level which is app objects.

Qlik Sense security hierarchy: Stream => Application => App Objects (sheet, chart, dimension and measure)

My goal is to restrict an user to a particular Stream => application => sheet =>Chart( set of charts).

Note: There is nothing hard coded in Qlik Sense server, you can modify it anyhow by disabling the default rules and write your own custom rules.

Please avoid alter or deletion of any default security rule in QMC. Disable concern security rule and make a new one for testing or production use.

Security to MasterObject or AppObject is the deepest or bottom level security.

.

There are some names I am taking for this exercise:

User: JARVIS

Stream: Jarvis to Stream

Application: Consumer_Sales (Default application comes with installation of Qlik Sense Desktop) which has 5 sheets.

Sheet: Budget Analysis (This sheet which will be visible to our user or set of users only)

AppObject: charts, dimensions, measures, stories etc.

For next step:  =>

Implementation Steps:

1.    Install Qlik Sense server on your machine(Use a dedicated user as service user). Verify its prerequisites and enable it  license then  you will find two desktop shortcuts which are QMC and HUB. Please use local administrator account for installation.

1.    Open QMC with service account(by which you installed QSE) and go to User Directory Connector and create a new connector, if Active directory is there in network, select add path of AD (active directory), => remove checked sign from sync user data from existing users => click okay and click on SYNC button. You will see all the user will show in USERS tab. If you want to add local server users to Qlik, just hit the MachineName/HUB URL from the concern user and the same user name with machine name as USER DIRECTORY will appear to USER TAB in QMC.

3. Go to Apps tab import your any application (.qvf file), I am importing Consumer Sales(which comes by default with installation of Qlik Sense Desktop and quite famous across everyone ).

4.1 Go to security tab and disable default STREAM rule, This stream says The user should see the resource if he/she has read access to the stream it is published to  means if any user or group of user has READ access to the stream then user will see all the Application and AppObjects(All resources) so if we try to restrict any user to any particular application or App Object ,it will always let user to see everything which comes in the stream. So by disabling it we are taking all the access from user to see anything in stream by default.

4.2.   Create a new stream with name "Stream for Jarvis" then click on apply => It will give you a warning in yellow color for  not to  basic securities then click on cancel for not to apply any security rule or user at this time.(Please verify in Security Rule tab that no custom stream security rule is not built automatically )

5. Go back to Apps tab, make a duplicate of your application(We generally first make duplicate then publish to any stream because once we publish we can’t do any changes or development on the same application ).We will use the same application which was duplicated to new one for reload, Here I am making duplicate of Consumer_Sales app and renaming it to Consumer_Sales to Jarvis and publish it to stream Stream or Jarvis.

6 Now login with JARVIS (with the concern user), you will see there is no stream with name Stream for Jarvis. So, what is the problem. Problem is, User JARVIS doesn't have connection with Stream for Jarvis. So, we will use custom properties to associate a user to resource(Stream) or I can say we need to write a security rule which will say JARVIS can see the stream Stream for Jarvis. For basic knowledge of custom properties

https://help.qlik.com/en-US/sense/3.1/Subsystems/ManagementConsole/Content/custom-properties-overview.htm

7. Go to Custom Properties tab, Create new  with name StreamLevelManagment with resource type Stream and User and give any  sample  value like  Assistant to it and assign to concern user(JARVIS)  by going to USERS tab , select JARVIS and on right side you will find custom property option , click on it and  then click on the space bar and you will see that sample value or what I took Assistant is appearing there select it  and do the same exercise for stream(Stream for Jarvis) by going to stream tab.

8 Now our task is to map Stream for Jarvis to user JARVIS by creating new security rule (Stream Template) as :

((user.@StreamLevelManagement=resource.@StreamLevelManagement))

I am describing security rules in Basic and Advance mode both to be sure you will not confuse how to make rule in both of Modes.

Now login with user JARVIS, you will find Stream for Jarvis.

I am describing security rules in Basic and Advance mode both to be sure you will not confuse how to make rule in both of Modes.

Now login with user JARVIS, you will find Stream for Jarvis.

9. Go back to Administrator, create a new custom property AppLevelManagment with resource type User and Apps then give a sample value to it and assigned this custom property to concern Users and Apps which you have been created for this exercise to make JARVIS can see the concern application as:

((user.@AppLevelManagment=resource.@AppLevelManagment))

Note: In this document, I am not focusing on ACTIONS under security rules such create, delete, publish, change owner etc. You need to concern for actions when client ask for it like my first set of user ca edit or duplicate sheet and do self service and another set of user can't.

10. Now you will see Jarvis Can see particular application but with all the sheets, but our goal is, to restrict JARVIS to only one sheet, for that create another security rule with App.object template and configure as:

((user.name="Jarvis" ) and resource.name="Budget Analysis")

Above rues says , user JARVIS can see Sheet type object and object is Budget Analysis. Now, login with JARVIS, you will see Jarvis see "Budget Analysis" sheet only.

Important point: Above security rule will disable all the sheets and you will see only "Budget Analysis" sheet, it means those sheet which will be published by your Qlik site member in same application under community section will not be visible to you.

Now if you wana your user see only see "Budget Analysis" sheet and those sheets which is shared by other users(when any user has rights to edit and publish a base sheet ) so here you need to write just opposite condition of what is written above as:

((user.name="Jarvis" ) and resource.name != "KPI Dashboard" and and resource.name != "Sales & Margin Analysis" and resource.name != "Sales Analysis" and resource.name != "Sales Rep Performance" and  resource.published="true")

Don't be so happy on this stage, JARVIS is restricted to only one sheet out of 5 sheets but when you open "Budget Analysis" sheet, JARVIS can't see any charts or objects and invalid object error message will be coming on the place of charts.

Here, you have two ways,

1. All the charts will be visible on that restricted sheet("Budget Analysis").

2. You want to restrict your user to any chart particular chart and hide other charts to user(JARVIS).

1. All CHARTS VISIBLE TO USER ON RESTRICTED SHEET

Let’s take all the objects (charts, filters) are supposed be visible on the "Budget Analysis" Sheet.

Create a new security rule:

1. ((user.name="Jarvis" or resource.name="*" and resource.objectType!="sheet"))

Then

2. ((user.name="User1" and resource.objectType="sheet"))

By above set of two security rule this User1  or JARVIS will see all the sheets on which he has access.

Here you need to write the above rule every time for each user to say user can see all the objects rather sheet its better to go with below instruction:

Important Note: Create a new rule with AppObject as resource type:

((resource.resourcetype = "App.Object" and resource.published ="true" and resource.objectType != "app_appscript" and resource.objectType != "loadmodel") and resource.objectType != "sheet" and resource.app.stream.HasPrivilege("read"))

Now, user can see all the charts are visible. First mile stone has been achieved.

Above rule says , anyone who has access to stream and published application, he can view all the application objects except sheet and for sheet you may use POINT NO 10 instruction. This rule will work for all the users , you just need to tell which user can see which sheet and lets say there are other users who can see all the sheets , then you may write one more rule here :

Now, the complex one, what if your client say, I wana my user will restrict to a sheet or set of sheets but he or she can see only one chart on the sheet and rest of the chart will be invisible.

ONLY ONE CHART WILL BE VISIBLE:

After step 10, next step :

Write security rule which grant access of App Objects of this sheet to JARVIS, Create a new security rule and configure it as JARVIS will see only one chart present in BUDGET ANALYSIS, by writing the code as:

((resource.objectType="masterobject" or resource.name="Sales $ by Product Group (sorted by Budget $)" or resource.name="Sales $" or resource.name="Product"))

If you can notice, Now I have taken concern all object Chart Name, Measure and Dimension which made that concern chart as resource.name.

Now you can see JARVIS can view only Sales $ by Product Group (sorted by Budget $) chart and rest are coming as Invalid Objects.

Important Note: Once you configured a Qlik Site for security every time whenever you add new user you have to give access for stream, application and objects(Sheets or Charts) in short need to update the custom security rules in QMC. New user by default can't see anything except STREAM if you add it to any stream as we do generally.

JARVIS stands for Just A Rather Very Intelligent System

After finishing Qlik Sense Enterpise , QAP(Qlik Analytical Platform) comes in picture which is for external users where number of users are in hundred and your client doesn't  want buy hundred of token so QAP is the right solution which has core base licencing.

You may get all the information step wise on the following document: QAP (Qlik Analytical Platform)

Other important Qlik Sense solutions:

1. Dynamic Sheet Exception With Stream and App Level Security
   
2. Best Practices of Qlik Sense Multi-Node Setup, Load Balancing & Maintenance(Let's Discuss Here) 
3. How to Design Simple Linear Regression in Qlik Sense with help of Microsoft R(R Integration with Qlik Sense ) 
4. How to delete a sheet via Qlik repository API using Postman Desktop 

Reach out to me at kumar.rohit1609@gmail.com if there is need of any clarification or assistance 

Connect with me on LinkedIn  https://www.linkedin.com/in/rohit-kumar-67ba152b/

To get latest updates and articles, join Qlik community at  https://www.facebook.com/QlikIntellectuals

When applicable please mark the appropriate replies as ACCEPT AS SOLUTION and LIKE it. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as LIKE if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as LIKE if you feel additional info is useful to others.

A great primer to Section Access by Gustav Guldberg of QlikView Support.

Power Tools for QlikView

Version: 1.2

Download here

https://www.dropbox.com/sh/8hac72aljgqlr7d/AAASkzbM2H1OoVcLE_wa0kWGa?dl=0

For the absolute latest version, please see Power Tools for QlikView - One-stop-shop

In this release:

• QlikView Server Agent *updated* - 1.6.2
• QlikView Server Super Agent *updated* - 1.7.2
• QMS API Client - 1.0
• Qvs Detector - 0.9
• Qv User Manager *updated* - 1.0.1
• Reload Schedule Migration - 1.0.4321
• Server Object Handler *updated* - 2.0 - Updated to 2.1 @ 2014-03-18 11:00
• Server Object Handler Batch *updated* - 2.0
• Shared File Viewer *updated* - 1.5.2 - Updated to 1.5.3 @ 2014-02-21 09:32
• Qv Server Object Connector *updated* - 1.5
• XML Db Viewer - 1.0.4321
• Shared File Repair *new* and *updated* - 1.1 - Updated to 1.2 @ 2014-06-30 21:29

Release notes here: Power Tools 1.2 for QlikView - Release Notes

Readme here: Power Tools 1.2 for QlikView - Readme

Patches

• Shared File Viewer: Fixed broken "Color on top" sort option, new version 1.5.3
• Server Object Handler: Fixed credentials being passed wrong, causing no documents to show in list, new version 2.1
• Shared File Repair: Fixed "The path is not of a legal form" error when selecting Qvs binary, added "Don't show this" option in startup notification, fixed fonts", new version 1.2

Want to collaborate?

Do you have questions, thoughts or suggestions? Maybe you want to report a defect in the Power Tools? Please post it in the Management forum on QlikCommunity: http://community.qlik.com/community/discussion-forums/management

Also check out our Twitter: @QvPowerTools

DISCLAIMER

Power Tools are a collection of software programs and tools used for troubleshooting purposes only. Power Tools are provided free of charge and are not supported. Power Tools are not official QlikView products and are provided without warranty. Use of Power Tools is entirely at the user's own risk.

QlikView has an intelligent algorithm to recognize dates independently of which region you are in. In most cases, you will have no problems loading them. It just works and you do not need to think about it. However, in some cases dates are not properly recognized and then you need to add some code in the script to make it work.

HIC

Hi All,

Sometimes there may be requirements from users where they want to see the charts in from certain point of time like YTD, QTD, MTD, Last 5 Years etc., please find the set analysis expressions for this type of scenarios.

YTD Sales (Year To Date)

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=YearStart(Today()))<=$(=Today())’}>} Sales )

Note: Year=, Quarter=, Month=, Week= excludes the selections in Year, Quarter, Month and Week dimensions.

QTD Sales (Quarter To Date)

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=QuarterStart(Today()))<=$(=Today())’}>} Sales)

MTD Sales (Month To Date)

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=MonthStart(Today()))<=$(=Today())’}>} Sales)

WTD Sales (Week To Date)

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=WeekStart(Today()))<=$(=Today())’}>} Sales)

Last 5 Years Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=YearStart(Today(), -4))<=$(=Today())’}>} Sales )

Last 6 Quarters Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=QuarterStart(Today(), -5))<=$(=Today())’}>} Sales )

Last 12 Months Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=MonthStart(Today(), -11))<=$(=Today())’}>} Sales )

Last 15 Weeks Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=WeekStart(Today(), -14))<=$(=Today())’}>} Sales )

Last 10 Days Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=Date(Today()-9))<=$(=Today())’}>} Sales )

Yesterday Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘$(=Date(Today()-1))’}>} Sales )

You can also arrive some flags for above scenarios in script and you use those flags in Set Analysis expression if your data always based on Current Date. Refer below link created by Richard.Pearce60‌

Calendar with flags making set analysis so very simple

Hope this helps.

Regards,

Jagan.