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.

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

What is Control Chart?

The control chart is a statistical analysis used to study how a process changes over time. Data are plotted in time order. Control charts are used to routinely monitor quality or simply you can say, control chart is process improvement technique.

There are many types of control charts, but we will focus on the basic control chart using the standard deviation method to understand the concept.

The following are some scenarios where control charts can be applied:

1. In manufacturing, to test whether machinery is producing products within specifications.

2. If you want to reduce the time it takes to admit a patient to the hospital.

3. To analyze how the number of views is increasing or decreasing for a particular website.

Components of Control Chat

1) Avg line

2) UCL (Upper Control line) = Avg Value + Std Deviation* Std Dev Multiplier

3) LCL (Lower control line) = Avg Value - Std Deviation* Std Dev Multiplier

So, basically we can see which are the points "Out of Control" or "In Control".

You can control your Std Dev multiplier using variable.

How to Create a Control Chart:

1. Create a Combo Chart.

2. Select a Time Dimension: Choose a time dimension that suits your analysis; for this example, I have selected MonthYear. Also, ensure that it is set to continuous in the axis settings.

3. Create Two Expressions: Create one expression for the line and another for the symbol.                              

   

    

4. Create expressions for the average line, Upper Control Limit (UCL), and Lower Control Limit (LCL), which will be used as reference lines in the chart. To set these reference lines, go to Chart Properties and navigate to the Presentation section.  

   

    

5. Create the Background color setting for the Symbol expression

         if( sum(Sales) < vUCL and sum(Sales) > vLCL, LightGreen(), LightRed())

Finally, the chart will appear as shown below

Please refer to the below link for further details

QlikView Control (SPC) Chart

Additionally, please see:

Benford's law

Bollinger Bands

When we want to do something with QlikView, we face so many possibilities to achive the goal. And sometimes, the first idea we have is not necessarily the simplest one.

I wanted to do create YTD and moving totals measures, difference (absolute and in percentage) of these moving totals versus the Year ago. QlikView is a BI tool: no problem I thought. Well, it was not so simple ...   The expressions became so complex that I finally did not understand them any more!

After some searches and many tries,the solution to such a chart is very simple. You just need to look at the right place.

As you can see, this chart contains actual data and the one of the year ago, a YTD starting in January and the same for the Year Ago, Rolling 3 and 12 periods and the same for the Year Ago. These values are contained in a TIME ANALYSIS axis that the user can select in a simple List Box to display only YTD and Rolling12 for example:

The document will help you to understand the modification you have to do to your data model to make this report very simple. As you can already see, the expression is the simplest expression in QlikView: sum(VALUE).

This simplicity let you afterwards mix the different measures of the database with this TIME ANALYSIS axis : get the current sales values, but the share the products represents the last 12 motnhs, and a difference vs year ago:

Many thanks to G. Wassenaar and his post that helped me to understand where to look exactly :  http://community.qlik.com/docs/DOC-4252

Have a nice reading

Fabrice AUNEZ

I have attached sample QVW and Sample data. Hope this will be helpful for all..

Have you ever wanted to create a Gantt chart in QlikView, only to find out that this chart type is not one of the pre-defined charts? Then you should be happy to learn that it is possible to create a Gantt chart and that it is not very difficult. See more on Recipe for Gantt chart.

HIC

Hi All,

This document helps you in loading multiple excels and excel sheets with the name of the sheet and data.

//to read each file from the specified folder

FOR EACH file in FileList('filepath\*.xlsx');

//In order to get the file information from SQLtables command making use of the ODBC connection format
ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

tables:
SQLtables;
DISCONNECT;

FOR i = 0 to NoOfRows('tables')-1
LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));
Table:
Load * ,
FileBaseName()as FIle,
FileDir() as Dir,
FileName() as File_Name,
'$(sheetName)' as Sheet_name
From $(file)(ooxml, embedded labels, table is [$(sheetName)]);

NEXT i

Next

Hope this helps !!!

please find the attachment for the eg: qvw and test fiels

Regards,

Avinashelite‌

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

Esta nota está publicada en el blog Qlikview en Español

Viene de una pregunta del foro publicada aqui

Si se tiene una tabla de ventas y se quiere predecir ventas futuras, teniendo algo como:

Para esto puede usarse la siguiente técnica que usa la ecuación de la recta:

PASO 1. Cargar datos de ejemplo:

SET NULLINTERPRET='';LOAD * INLINE [
    Fecha,     Venta
    1/2/2014,    11
    1/3/2014,     3
    1/4/2014,    10
    1/5/2014,     9
    1/6/2014,    11
    1/7/2014,     5
    1/8/2014,     8
    1/9/2014,    10
    1/10/2014,   11
    1/11/2014,   16
    1/12/2014,
    1/13/2014,
    1/14/2014,
    1/15/2014,];

PASO 2. Crear gráfico de linea con:

Dimensión:  Fecha

Expresión 1: (Poner "Pronostico" como etiqueta de expresión)

linest_m(total aggr(if(sum(Venta),sum(Venta)),Fecha),Fecha) // Pendiente

*Fecha // Mes a predecir

+linest_b(total aggr(if(sum(Venta),sum(Venta)),Fecha),Fecha) //Intercepto 

Expresion 2:

If(IsNull(Venta), Pronostico,sum(Venta))

PASO 3. En Propiedades de Grafico>Expresion desmarcar todas las opciones para la expresión 1, y marcarLinea para la expresion 2.

Queda así:

PASO 4. En el atributo Tipo de Linea de la Expresion 2, poner: if(IsNull(Venta),'<S2>')

Quedaría así:

El qvw de ejemplo puede descargarse aquí

Referencia:
http://community.qlik.com/thread/16846

Hi,

I have seen many posts on community for showing negative values on charts like Block chart, pie chart etc. There is a solution available on community, but I thought to create the document so that everyone can utilize the same.

Lets say I have below data set

LOAD * INLINE [
Country, Score
US, 4971.765
UK, 4782.54
Japan, -200.941
India, 1412.181
Russia, 2445.292
];

Now I want to create the Block chart with above data set. So create the block chart with dimension as country and expression as sum(Score) , as soon as you create the chart you will get the error message that "positive and negative values in chart". But you can still display the negative values on chart with slight modification in expression.

Steps to create the block chart with negative value

1) Create the block chart

   Dimension : Country

   Expression : dual(sum(Score),fabs(sum(Score)))

If you want to display the distribution in % then use below expression

dual(num(sum(Score)/sum(TOTAL Score),'#0.00%'),fabs(sum(Score)))

Using dual function you can display the negative value with the base as positive value.

2) Go to chart properties -> Number Tab and check the Number format setting always as "Expression default". If the number format setting is not the expression default then negative values will be displayed as positive and also data will be displayed in wrong format. So if you want to provide any format like %, money you can define in the Dual expression itself using num function

Please see the attached application. In this application I have used the Fast change option so that you can see the results for multiple chart type as well

see also,

Control Chart

Benford's law

Bollinger Bands

Rolling N months

Regards,

Kushal

The Most vital Set Analysis that we are using in creating our reports. Why we don't make it simple! This document will make it simple and straight forward to everyone. Good luck

Hello Everyone,

I have attached the document for the important Qlikview functions used in script as well as in UI.

Please have a look and also feel free to update the document or comment in the session for the functions which is missed.

As QlikView applications grow the number of tabs that information is spread across can grow rapidly as well.   The new Ajax view makes it easier to navigate when there are many tabs (with the drop down) but if users are using the IE Plugin or an older version of QlikView a lot of the screen can be taken up with tabs.

One way of solving this is to group the tabs into functional areas and place a menu on the welcome tab that allows the user to select which functional area they want to look at.

Furthermore, if some tabs are simply not relevant to some users then it is possible to hide those tabs (and the menu options to show them) from those users.

This document gives an example of a menu that switches tabs on and off and implements hiding of tabs from users based on their OSUser name - loaded from an Inline table in the load script.

I hope you find this document useful, you can find links to other documents I have uploaded here: http://www.quickintelligence.co.uk/qlikview-examples/

Steve

http://www.quickintelligence.co.uk/qlikview-blog/

Ejemplos de Función Date# y Date para transformar fechas y luego darles formato. Esto como método alternativo a MakeDate, que usa 4 funciones haciendo más lento el load:

MakeDate(Left(20130628,4), Mid(20130628,5,2),Right(20130628,2))

1. Date#(20130628,'YYYYMMDD')

Devuelve 41453 en formato 20130628

2. Date(Date#(20130628,'YYYYMMDD'),'DD-MMM-YY')

             Devuelve 41453 con formato 28-jun-13

3. Date(Date#('2013.06.28','YYYY.MM.DD'),'DD-MMM-YY')

             Devuelve 41453 con formato 28-jun-13

4. Date(Date#('06-28-13','MM-DD-YY'),'DD-MMM-YY')

             Devuelve 41453 con formato 28-jun-13

5. Date(Date#('28.06.13' ,'DD.MM.YY'),'DD-MMM-YY')

              Devuelve 41453 con formato 28-jun-13

6. Date(Date#('jun.28.2011', 'DD.MMM.YYYY'), 'DD-MMM-YY')

              Devuelve 41453 con formato 28-jun-13

7. Para eliminar hora de una fecha

Date(Floor( '2013-06-28 07:30:38' ),'DD-MMM-YY')

                     Devuelve 41453 con formato 28-jun-13 sin minutos

8. Para obtener solo la hora de una fecha

Time(Frac( '2013-06-28 11:30:38' ),'hh:mm:ss')

     Devuelve 0,47960648148 con formato 11:30:38

9. Para obtener Fecha y Hora

             Date( Timestamp#( '20130628 11:30:38', 'YYYYMMDD hh:mm:ss'), 'DD-MMM-YY hh:mm:ss'   )

                      Devuelve 41453,479606481  con formato 28-jun-13 11:30:38

10. Para obtener día de la semana en fecha

             Date( '2013-06-28', 'WWW DD-MMM-YY')

                      Devuelve 41453  con formato  vie 28-jun-13

11. Resta un mes a la fecha

AddMonths('2013-06-28',-1)

Devuelve 41422 en formato 28/05/2013 (formato predeterminado)

12. Suma un mes a la fecha

AddMonths('2013-06-28', 1)

Devuelve 41453 en formato 28/07/2013 (formato predeterminado)

Esta nota esta publicada en el blog: Qlikview en Español

NOTAS:

Al indicar un formato de hora en Qlikview la letra mayuscula M= mes,  y letra minuscula m= minutos

Referencias (ingles)

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/19/why-don-t-my-dates-work

http://community.qlik.com/docs/DOC-3102

This QlikView shows how basic Set Analysis syntax can be used to deliver a Prior Period Comparison.

Rather than taking the approach of flagging rows as being in certain periods at load time this example shows the comparisons based on selections.  It is an approach I have used many times on client site and tends to offer the users what they require.

The QlikView was originally written as a response to the following QlikCommunity thread: http://community.qlik.com/message/102230

If you want to copy and paste the table from this example into another document you can set all the variables by adding this code to your load script:

let vMaxDate  = '=max(Date)';

let vMaxDay   = '=day(max(Date))';

let vMaxMonth  = '=month(max(Date))';

let vMaxYear  = '=max(Year)';

let vPriorMonth  = '=month(addmonths(max(Date),-1))';

let vPriorMonthYear = '=Year(addmonths(max(Date),-1))';

let vPriorYear  = '=vMaxYear-1';

let vPriorYearDate = '=date(addyears(max(Date),-1),' & chr(39) & 'DD MMM YYYY' & chr(39) & ')';

Obviously you will need to change the field names to match the date fields in your data model.

There is a Qlik Sense version of this application available for download here:

Qlik Sense App: Prior Period Comparison with Set Analysis

There are a number of other tutorials and downloadable examples on our website here:

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

If you have any questions please post them in the comments below.

Steve Dark

www.quickintelligence.co.uk

This technical brief explains the Bundle Load function in QlikView. The files contained in this zip file are the technical brief, a QVW, and the supporting data and image files. I wrote a blog about this topic and you can view it here.

I developed the QlikView Data Analyzer Application with features to do Data profiling, Rapidly analyse and validate the data in the Data model of a QlikView Application. I have added Macros & Buttons to the layout to quickly create List boxes and Table boxes based on the values selected in the Field Name and Table Name list boxes. There is also a button to create empty Text Boxes for quickly writing any expressions while analysing and validating the data. Once the analysis is completed the layout can be cleaned up by clicking on the respective Delete Object buttons.

All the objects in the Data Analyzer Tab can be copied into any QlikView Application.

Follow these steps for copying the objects into any QlikView Application:

1. Create a new Tab in your existing application and rename it as "Data Analyzer"
2. Copy all the objects in the "Data Analyzer" tab of QlikView Data Analyzer Application into the "Data Analyzer" tab of your application
3. Press CTRL + M in the QlikView Data Analyzer Application and copy all the Macro code and paste that macro code into the Macro window of your application. (You can also find this code in the attached Macro code Text file attached below)

Select only those fields that are required in the Table Box Feature

In the above example, the Address Table contains 9 fields, of which only 3 fields were selected and when clicked on Create Table Box Button, it will generate a Table box with only 3 fields that were selected. This feature helps to load only few required fields from a large table. Multiple field names can also be selected by holding CTRL Button and selecting them in the Field Name list box.

Script to Load All the Tables in a SQL Server Database

Also in the Script, I wrote the code that can be used to connect to any SQL Server Database and load all the tables in that Database and generate QVDs. The QlikView Analyzer functionality can be used to quickly do data profiling and analysis of the data in that Database. For this application I used Microsoft SQL Server Adventure works database.

I have developed the QlikView Data Analyzer Application with inspiration from the Generic Data Profiler App built by Steve Dark,

Thanks Steve 

Note 1: This macro code creates Two variables, to enable creating list boxes and Table boxes dynamically:

1. vQVDA_Fields
2. vQVDA_LB_Field

So please avoid using these two variable names for any other purposes in your App when incorporating Data Analyzer functionality.

Note 2: As most of the times, macros are not advised for the production environments, its better to use the Data Analyzer functionality only in the QlikView Applications in the development environment and not to incorporate in the production applications

Sandeep Vangala

Founder & CEO

SANRIDGE LTD

London, UK

www.sanridge.com

This example shows how to set a variable using an action on a text box.  That same variable is used to dynamically change the colour of the text box - feeding back that a selection has been made.

The variables once set can be used as part of expressions (including set analysis) or for determining layout - such as showing or hiding certain objects or tabs.

The QlikView was originally written as a response to the following QlikCommunity thread:

http://community.qlik.com/message/209378

I expand on the use of Actions on buttons, text areas and gagues in my blog post on the subject: QlikView Buttons - Where, Why and How

Steve Dark

www.quickintelligence.co.uk