Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Qlik Community is the global online community for Qlik employees, experts, customers, partners, developers and evangelists to collaborate.
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 memb
...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 Se
...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).
These questions and others are answered in this Technical Brief.
Thank you Barry for good discussions.
See also the following blog posts
Creating a Date Interval from a Single Date
Creating Reference Dates for Intervals
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(DateF
...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
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
...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:
See more in the attached files.
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.
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
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
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 prod
...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:
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.
Why You sometimes should Load a Master Table several times
Fiscal Calendar with Non-Standard Days (Not 1-31)
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:
Often are additionally relative and/or continuing time-dimensions and flags very helpful to avoid complex calculations:
Creating Reference Dates for Intervals
Calendar with flags making set analysis so very simple
Period Presets: Compare Periods on the fly
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:
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!
ps: within the attachment is a german translation - deutsche Fassung.
Power Tools for QlikView
Version: 1.2
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:
Power Tools for QlikView
Version: 1.2
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:
Release notes here: Power Tools 1.2 for QlikView - Release Notes
Readme here: Power Tools 1.2 for QlikView - Readme
Patches
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 proper
...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.
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)
S
...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.
Searching in field values is a powerful feature in QlikView (all this should also work in QlikSense, but I haven't tested everything). You can use searches for example in set analysis field modifier, select in field actions and not to forget filterin
...Searching in field values is a powerful feature in QlikView (all this should also work in QlikSense, but I haven't tested everything). You can use searches for example in set analysis field modifier, select in field actions and not to forget filtering e.g. list boxes in the frontend.
Hence knowing the power of searches is vital to developers as well as end users.
Unfortunately (in terms of 'getting started with search'), there are several possible search modes, have a look at The Search String to get an overview.
Text searches continues explaining. Some of the search modes are explained a bit more detailed: The Expression Search
And there are some things to consider we would hardly know without following Henric Cronström's ( hic ) great design blog: Search - But what shall you find?
Not to forget, there is a nice cheat sheet: Qlik Sense Search Cheat Sheet, covering some features, but not everything.
There is also of course some documentation in the HELP file as well as the reference manual, but not what I would call a comprehensive documentation of this essential product feature. Especially, there is few documentation about the so called 'compound search'.
So let us shed some light on
QlikView's Compound Search feature
First, let's create some sample data:
SAMPLE:
LOAD RecNo() as Number, Text
INLINE [
Text
Harry
Sally
Harry & Sally
New Hampshire
New York
Something new
UPPER
not upper
Al Bundy
Airport Newark
me
];
[Note: As Henric commented to one of his blog posts, there is no escape character to escape a wildcard, hence if you want to search for e.g. '?', you would need another search mode, e.g. advanced search using =index(FIELD,'?') ]
Another example for the XOR operator would be to find the complement of a search (for example search for *Sally*):
You could achieve this by selecting (Sally), then use the context menu to select excluded, but you can also use a single search: (* ^ *Sally*)
[Agreed, the syntax would be easier if there was an operator for NOT or complement, but I haven't found it so far. If you know it, please drop a comment below.]
All these operators can be used to not only combine two, but many searches: (me|Harry|Sally)
This would be a good point to end this document. But there is something more I haven't read about so far:
You can achieve similar without nesting, but take care of the order of your searches and operators:
Some (or all?) of these results can of course be achieved using other search modes, I just wanted to focus on the compound search mode in this blog post. I hope all mysteries of the compound search have been revealed.
If you have any comments or questions, drop a comment below.
Have fun!
edits:
2016-02-02: Complement can be created easier, just using (* ^ *Sally*), added some more examples, reorganized some parts
This tutorial presents a script pattern for assigning time dimensions to multiple fact dates in a data model. It answers the commonly asked Forum question "how do I link to two dates"?
The pattern will demonstrate how to link all fact dates to a commo
...This tutorial presents a script pattern for assigning time dimensions to multiple fact dates in a data model. It answers the commonly asked Forum question "how do I link to two dates"?
The pattern will demonstrate how to link all fact dates to a common calendar as well as using separate calendars for each fact date.
When you are using many tab sheets within QlikView your application can easily become unclear and very full. Using standard tab sheets it is not possible to build a hierarchy in your menu.
I have built a dropdown menu extension that allows you to buil
...When you are using many tab sheets within QlikView your application can easily become unclear and very full. Using standard tab sheets it is not possible to build a hierarchy in your menu.
I have built a dropdown menu extension that allows you to build a hierarchy within your menu and manage multiple tab sheets in a structured way. The menu is easy to install and use, you can configure it from within your QlikView application.
You can find documentation, a sample qvw file and the extension itself at the following location:
petra-bloem/dropdownMenu · GitHub
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 SQLta
...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,
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
...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
This is something that's been asked a number of times, so I would like to share this small tip for everyone. interesting in reading data from Sharepoint lists into QlikView.
There are a number of ways to extract data from Sharepoint.
1. Using WebServic
...This is something that's been asked a number of times, so I would like to share this small tip for everyone. interesting in reading data from Sharepoint lists into QlikView.
There are a number of ways to extract data from Sharepoint.
1. Using WebServices
2. Using RPC-Calls
3. Using Microsoft Access as a Data Bridge
The most common way is using Webservice-calls. However constructing XML-request isn't all straight-forward and can't be done directly in QlikView.
An easier way is to use RPC-calls using owssvr.dll, like:
listGUID and viewGUID must be in the format {XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX}.
The listGUID can easily be found by navigating to the list in Sharepoint and go to List Settings. The listGUID will then show up in the address-field in your browser.
If viewGUID is omitted, the default view will be retrieved. To find the viewGUID locate the list in Sharepoint and activate the view needed. Select View Soure in IE to see the source code. Search for view={ to locate the viewGUID.
The response will be in XML, so when reading into QlikView make sure you select XML Files and Internet File. Add the correct crafted URL as described above. The data retrieved can be found in the node xml/data/row.
Example URLs to be used in QlikView:
http://your-sharepoint-server/site/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List={B9C20FC4-4C04-4F4C-93EF-E69E11D286F2}
http://your-sharepoint-server/site/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List={B9C20FC4-4C04-4F4C-93EF-E69E11D286F2}&View={58678A30-DC12-4C66-8568-28E4D9A3EED1}
For more info about owssvr.dll use Google to find various ways of filtering the information
Note: Only fields defined in a view can be retrieved. You need to construct the view with the fields needed, if not using the default view.
You can retrieve data from sharepoint using Microsoft Access.
If you are looking at a sharepoint list, click at
Actions --> Open with Access
Microsoft Access opens and a window "Open in Microsoft Access" appears. Select a folder and choose the option "Link to Data on the Sharepoint Site".
Afterwards you can connect from QlikView to this access database via ODBC and you can easily read all fields of the sharepoint list with a SQL Query.
This disadvantage of this approach is, that you'll always have to store your MS Access Database with your QlikView-Sheet. In a server environment this isn't a big problem, but with local clients loading data, this approach has got its shortcomings.
Hi All,
The script below loads the data into qlikview file for multiple sheets in a single load by using the Loop statements.
LET vStartSheetNumber = 1;
LET vEndSheetNumber = 50;
LET vExcelFileName = 'Data';
// Generate Empty table
Data:
LOAD
'
...Hi All,
The script below loads the data into qlikview file for multiple sheets in a single load by using the Loop statements.
LET vStartSheetNumber = 1;
LET vEndSheetNumber = 50;
LET vExcelFileName = 'Data';
// Generate Empty table
Data:
LOAD
'' AS Data.Field1,
'' AS Data.Field2
AutoGenerate(0);
FOR index = vStartSheetNumber TO vEndSheetNumber
Concatenate(Data)
LOAD
*
FROM [$(vExcelFileName).xlsx]
(ooxml, embedded labels, header is 10 lines, table is [Page $(index)]);
NEXT
Note : in my excel file the sheet names are in the patter of Page 1, Page 2, ..... Page n.
Hope this helps others.
Regards,
Jagan.
Me and a colleague have put together a new Calendar, combining a super fast method for generating the needed calendar, while also removing a lot configuration options in the previous methods.
So if you're using a Min/Max based approach like the one
...Me and a colleague have put together a new Calendar, combining a super fast method for generating the needed calendar, while also removing a lot configuration options in the previous methods.
So if you're using a Min/Max based approach like the one described here: Creating A Master Calendar
Then you're properly used to waiting for the Min/Max to find the largest values in your dataset. This can take minutes. What we have here takes less than a second. I've actually not been able to time this, because this calendar script takes less than a 1 seconds even on billions of rows on decent hardware. The only exception to this is if you use a date range starting before christ and spans a thousand years. If you have a date range, with a lot of holes in it (dates with no data), then you should use the last parameter:
Call Calendar('[Date (NK)]','MyCal','Pre.','','true');
The Attached qvw shows both methods (remember the qvs file).
I've not commented it, because most people will not grasp what is going on anyway, and don't need to
To try it out, include the following in your script, and add this to your code:
Call Calendar('DateField');
/* ************************************************************************************
itelligence Standard Qlik Calender Torben Seebach/Martin Didriksen Special Thanks to Rob Wunderlich and John Witherspoon
Configure Calender parameters
Syntax:
Calendar(DateField[,CalendarName][,Prefix[,Suffix[,FullCalendar]]])
Example:
Call Calendar('Date (NK)','MyCal','Pre.','','true'); // creates a the MyCal table and fields are prefixed with Pre. In the superfast variat
Call Calendar('Date (NK)'); //creates a table called calendar based of "Date (NK)" field
Mandatory:
@_DateField Contains the name of the field to connect the calendar to
Optional:
@_CalendarName Contains the name of the calendar we create
@_CalendarPrefix A Prefix for all fields
@_CalendarSuffix A Suffix for all fields
@_FullCalendar If this contains a value then it creates a calendar with all dates from Min(Date) to Max(Date), default is the slowest option
************************************************************************************ */
Sub Calendar (_DateField,_CalendarName,_CalendarPrefix,_CalendarSuffix,_FullCalendar)
Let _StartTime = Now();
Let _CalendarName = If(Len('$(_CalendarName)')=0,'Calendar','$(_CalendarName)');
Let _CalendarPrefix = If(Len('$(_CalendarPrefix)')=0,'','$(_CalendarPrefix)');
Let _CalendarSuffix = If(Len('$(_CalendarSuffix)')=0,'','$(_CalendarSuffix)');
Let _FullCalendar = If(Len('$(_FullCalendar)')=0,1,0);
Let _DateField = PurgeChar(_DateField,'"[]');
"$(_CalendarName)":
LOAD
Distinct [$(_DateField)] as [$(_DateField)],
Text(Date([$(_DateField)])) as [$(_CalendarPrefix)DateText$(_CalendarSuffix)],
Year([$(_DateField)]) as [$(_CalendarPrefix)Year$(_CalendarSuffix)],
Week([$(_DateField)])&'-'&Year([$(_DateField)]) as [$(_CalendarPrefix)WeekYear$(_CalendarSuffix)],
Week([$(_DateField)]) as [$(_CalendarPrefix)Week$(_CalendarSuffix)],
Month([$(_DateField)]) as [$(_CalendarPrefix)Month$(_CalendarSuffix)],
Year([$(_DateField)])&'-'&Text(Date([$(_DateField)],'MM')) as [$(_CalendarPrefix)YearMonth$(_CalendarSuffix)], 'Q'&Ceil(Month([$(_DateField)])/3) as [$(_CalendarPrefix)Quarter$(_CalendarSuffix)],
AutoNumber(MonthStart([$(_DateField)]),'_MonthSerial') as [$(_CalendarPrefix)MonthSerial$(_CalendarSuffix)],
AutoNumber(QuarterStart([$(_DateField)]),'_QuarterSerial') as [$(_CalendarPrefix)QuarterSerial$(_CalendarSuffix)],
AutoNumber(weekyear([$(_DateField)]) &'|'&week([$(_DateField)]),'_WeekSerial') as [$(_CalendarPrefix)WeekSerial$(_CalendarSuffix)] ;
If _FullCalendar=1 Then
LOAD Date(_DateStart+(Iterno()-1),'$(DateFormat)' ) as [$(_DateField)]
While (_DateStart+(Iterno()-1)<=_DateStop);
LOAD
Floor(Min(Fieldvalue('$(_DateField)',RecNo()))) as _DateStart,
Floor(Max(Fieldvalue('$(_DateField)',RecNo()))) as _DateStop
AUTOGENERATE FieldValueCount('$(_DateField)');
Else
LOAD Num(Fieldvalue('$(_DateField)',RecNo())) as [$(_DateField)]
AUTOGENERATE FieldValueCount('$(_DateField)');
End If
/* clean variables */
Let _TotalTime = Round((Now()-_StartTime)*60*60*24,0.00000000001);
Let _StartTime = NULL;
Let _vDateStart = NULL;
Let _vDateStop = NULL;
Trace $(_CalendarName) created in: $(_TotalTime) seconds;
Let _TotalTime = NULL;
ENDSUB
Updated 2014-05-23:
Other than my brilliant Collegue Martin Didriksen, I also want to give John Witherspoon some credit for inspiration on the autogerate method used. I think he was the first who posted it online.
Working hours calculation is essential measure which is useful in many business cases. In this post I am going to demonstrate the steps to calculate this measure. I have also covered other aspects related to working hours like overtime hours and ex
...Working hours calculation is essential measure which is useful in many business cases. In this post I am going to demonstrate the steps to calculate this measure. I have also covered other aspects related to working hours like overtime hours and excluding lunch break hours.
Example in this post works for weekends as Saturday and Sunday. If you have any other weekends then refer to my below post
Working-days-and-hours-calculations-for-custom-weekends
Consider the below case
Let's say one Ticket is logged into the system on certain day , call it as Start Date, and the same Ticket got resolved on certain day, call it as End Day. Now we may want to calculate the working hours between these two Dates to calculate the efficiency of ticket closure time.
1) Calculate business working hours excluding week ends(Saturday and Sunday) and Holidays.
Below are the considerations
1) Count only standard working hours ( 9 AM - 6 PM)
2) Exclude Saturdays and Sundays
3) Exclude Holidays
Below is the Setup in QlikView
You can change the Date format of the below variables as per your convenience eg. 'DD/MM/YYYY hh:mm:ss' to 'DD-MMM-YYYY hh:mm:ss'
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss';
First, convert your Timestamp date format to 24 hour format using the below functions
a) Timestamp# () (if your Timestamp values are text)
b) TimeStamp () (If your Timestamp values are in proper Timestamp format)
Now you can setup the below variables for standard working hours in weekdays.You can change the Variable according to your working hours (Here 9 AM - 6 PM) and rest calculation will be done automatically
Now Setup the Holiday list as below. You can use your own holiday list
Below is the logic to calculate the business working hours between to dates
Here is the output
2) Calculate business working hours excluding week ends(Saturday and Sunday), Lunch Breaks and Holidays.
Below are the considerations
1) Count only standard working hours ( 9 AM - 6 PM)
2) Exclude Saturdays and Sundays
3) Exclude Lunch Break (1 PM - 2PM)
4) Exclude Holidays
Set the Variables for standard working hours and lunch breaks. You can change the values according to your needs
Include the Holidays
Below is the logic to calculate the business working hours between two dates
Here is the output
If you want to present the working hours in hh:mm:ss format then you can use below logic after calculating working hours
=interval(Working_Hours/24,'hh:mm:ss')
Please see the attached QVW.
Feel free to provide any suggestions.
Below script helps in loading all the tables in the database and store that tables in QVDs.
ODBC CONNECT TO MyODBC;
SQLTableList:
LOAD "TABLE_NAME" as TableNames;
SQL SELECT DISTINCT "TABLE_NAME"
FROM "INFORMATION_SCHEMA".COLUMNS;
Note:
Information schema
...Below script helps in loading all the tables in the database and store that tables in QVDs.
ODBC CONNECT TO MyODBC;
SQLTableList:
LOAD "TABLE_NAME" as TableNames;
SQL SELECT DISTINCT "TABLE_NAME"
FROM "INFORMATION_SCHEMA".COLUMNS;
Note:
Information schema.columns is for MS SQL Server database, you can replace that with the euqivalent table in the corresponding database.
For oracle ALL_TABLES ALL_TABLES
For MYSQL - all_tables or SHOW_TABLES
How to List All Tables and Describe Tables in Oracle, MySQL, DB2 and PostgreSQL | OneWebSQL
MySQL :: MySQL 5.7 Reference Manual :: 13.7.5.37 SHOW TABLES Syntax
Let vTableCount = NoOfRows('SQLTableList');
Let vTableCountQVD = 'dbo.[' & NoOfRows('SQLTableList') & ']';
For i = 0 To $(vTableCount) -1
LET vMyTableName = 'dbo.[' & Peek('TableNames', $(i), 'SQLTableList') & ']';
LET vMyTableNameQVD = Replace(Peek('TableNames', $(i), 'SQLTableList'), ' ', '');
$(vMyTableNameQVD):
SQL SELECT *FROM $(vMyTableName);
STORE $(vMyTableNameQVD) INTO $(ExtractQVD)$(vMyTableNameQVD).QVD;
DROP Table $(vMyTableNameQVD);
Next i
Hope this helps others.
Regards,
Jagan.