When building an app, there are certain functions that I use over and over again. This is the case when I need to add a Quarter field to my data model. I often have dates in my apps and sometimes I like to provide the user with a Quarter field so I thought I would share the script that I often use. Below is the script I always use to create a Quarter field in my data model.When creating the Quarter field, I use the Ceil function. The Ceil function can be used in the script or a chart and it rounds a number up to the nearest multiple of the specified step interval. You cannot talk about the Ceil function without mentioning the Floor function. The Floor function does the opposite of the Ceil function by rounding numbers down. Both functions have a third parameter, the offset, which allows you to increase or decrease the result of the Ceil or Floor function by a specified value. Let’s take a look at some examples.The syntax for the Ceil function is:Ceil(x[, step[, offset]])Ceil(2, 4) – returns 4. The step is 4 so you can think of these values 0, 4, 8 and so on. The number 2 is between 0 and 4 and when rounded up will return 4.Ceil(4,2) – returns 4. The step is 2 so you can think of these values 0, 2, 4, 6 and so on. The number 4 is the same as one of the step values so it returns 4.Ceil(5.5, 3) – returns 6. The step is 3 so you can think of these values 0, 3, 6, 9 and so on. The number 5.5 is between 3 and 6 and when rounded up will return 6.Ceil(5.5, 3, 0.5) – returns 6.5. The step is 3 so you can think of these values 0, 3, 6, 9 and so on. The number 5.5 is between 3 and 6 and when rounded up will return 6. Since there is an offset value, we need to increase 6 by 0.5, giving us a final result of 6.5.Ceil(4.111) – returns 5. There is no second parameter in this example, so 1 is assumed. You can think of the values 0, 1, 2, 3, 4, 5 and so on. The number 4.111 is between 4 and 5 and when rounded up will return 5.The Floor function does the same as the Ceil function except instead of rounding up, it rounds down. Let’s take a look at the same examples and see how the results vary.The syntax for the Floor function is:Floor(x[, step[, offset]])Floor(2, 4) – returns 0. The step is 4 so you can think of these values 0, 4, 8 and so on. The number 2 is between 0 and 4 and when rounded down will return 0.Floor(4,2) – returns 4. The step is 2 so you can think of these values 0, 2, 4, 6 and so on. The number 4 is the same as one of the step values so it returns 4.Floor(5.5, 3) – returns 3. The step is 3 so you can think of these values 0, 3, 6, 9 and so on. The number 5.5 is between 3 and 6 and when rounded down will return 3.Floor(1.1, 1, 0.5) – returns 0.5. The step is 1 so you can think of these values 0, 1, 2, 3 and so on. The number 1.1 is between 1 and 2 and when rounded down will return 1. Since there is an offset value, we need to decrease 1 by 0.5, giving us a final result of 0.5.Floor(4.111) – returns 4. There is no second parameter in this example, so 1 is assumed. You can think of the values 0, 1, 2, 3, 4, 5 and so on. The number 4.111 is between 4 and 5 and when rounded down will return 4.So now let’s apply the Ceil function to determine the quarter from a date. The script I like to use is:If we break this down, we first use the Month function to return the month number and then we divide that by 3 since there are 3 months in a quarter. So assuming the OrderDate is December 11, 2015, Month(OrderDate)/3 will return 4 (12/3=4). Then the Ceil function is used to round that number up. The script above also uses the Dual function so that we can display the Quarter as a string, Q4, with a numeric value of 4. This makes it easier to sort charts that use the Quarter field.The Ceil and Floor functions provide rounding capabilities with the ability to set the step as well as an offset. If I had a cheat list of scripts I like to use, this Quarter script would be on the list. What would be on your list?Thanks,Jennell ...View More

It was back in April at Qonnections, our 10th global partner conference that we unveiled Qlik Sense 2.0 and shared our platform strategy with the world. It was also the first time we talked in detail about our plans for QlikView 12. Today I’m delighted to be able to share the news that it’s arrived! QlikView 12 will you please stand up and show yourself to the world!There is no doubt that this is an eagerly awaited release by many of our 37,000 strong global customer base. But why? QlikView is a very mature product, it's functionally rich, and it’s undoubtedly in my opinion the product that revolutionized business intelligence and ultimately created the global data discovery market as we know it today. So what is so important about QlikView 12?An investment in QlikView 12 is an investment in QlikWith QlikView 12, Qlik delivers on its commitment to its proven, market-leading data discovery solution which secures our customers long term investment in the product. It also lays the foundation for our customers to partner with Qlik to build out their business intelligence strategies and meet the expanding needs of their BI consumers by addressing multiple use cases through a unique platform approach to visual analytics. QlikView 12 now runs on the second generation QIX (Qlik Data Indexing) engine that powers the entire Qlik portfolio. With this improvement, we can more easily help customers address new use cases in Qlik Sense by allowing them to share data models across the platform. Our investments also benefit the way our customers use QlikView today. QlikView 12 delivers a number of deployment, performance, security and connectivity enhancements along with greater accessibility through enhanced mobile touch-enabled capabilities. In addition QlikView customers will be able to now take advantage of Qlik’s strategy to deliver value added cloud services – such as Qlik’s “Data as a Service” offering, Qlik DataMarket. (If you want to see some of this in action check out this brief presentation)QlikView 12 - What's New PresentationQlikView - REST ConnectorQlikView - Qlik Data MarketPut simply, QlikView is a business intelligence solution with an unrivaled pedigree, functional richness and delivers the lowest cost of ownership in the market. Many customers have already delivered robust guided analytics and dashboards to knowledge workers across their organizations, and with QlikView 12, that investment is secured. Regards,Michael TaralloSenior Product Marketing ManagerQlik@mtarallo - follow me ...View More

In an earlier tutorial I described how to create a bootstrap drop down Mashup Editor - Create a Bootstrap Drop Down selection menuWhat if you need to create more than one drop down in a single page? For this, I have create a simple reusable object that you can call as many times as you need.Open the files from the previous tutorial and start making some changes.We will remove the function showData of Line 115 and the app.createList from Line 137.We need to add our data holders. var data = { hq: {}, rf: {} }; Then, our function that will be getting the list data. function getFieldData (field, callback, title) { app.createList({ qDef: { qGrouping: "H", qFieldDefs: [ field ], qSortCriterias: [{ qSortByAscii: 1, qSortByLoadOrder: 1 }], }, qInitialDataFetch: [{ qTop : 0, qLeft : 0, qHeight : 1000, qWidth : 1 }], qShowAlternatives: false, }, function(reply) { field = (title) ? title : field; data.hq[field] = reply.qListObject.qDataPages[0].qMatrix; refactorFieldData(field); callback(true); }); }; Refactor Data to a more readable format rather than qText etc. function refactorFieldData (field) { var tdata = []; $.each(data.hq[field], function(key, value) { if (value[0].qState!=='X') { tdata.push(value[0].qText); } }); data.rf[field] = tdata; }; Now, we need to add a function the will create the bootstrap drop down menu function createFieldFilter (field, fieldName) { fieldName = (fieldName) ? fieldName : field; if (data.rf[field].length > 1) { var $element = $('#dropdown'+field + ' ul'); $element.empty(); $.each(data.rf[field], function(key, value) { $element.append('<li><a data-select="'+ field + '" data-fieldname="'+ fieldName + '" data-value="'+ value + '">'+value+'</a></li>'); }); } }; Add the Html code. Make sure that the id starts with dropdown and have the Field name at the end <div class="dropdown" id="dropdownYear"> <button class="btn btn-default dropdown-toggle" type="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="true"> Select Year <span class="caret"></span> </button> <ul class="dropdown-menu scrollable-menu" aria-labelledby="dropdownYear"></ul> </div> Lets add the code that will bind all of these into one statement getFieldData('Year', function () { createFieldFilter('Year'); }); This will get all of the data for the field 'Year' => add it into the object data.hq['Year'] => refactor it into a more readable format => put into data.rf['Year'] => createFieldFilter will look for the id dropdown+field and add the elements.You can repeat the last javascript and html code for as many drop downs you would like to make. For this instance, I will recreate the 'Case Owner Group' from the last tutorialHTML <div class="dropdown" id="dropdownCaseOwnerGroup"> <button class="btn btn-default dropdown-toggle" type="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="true"> Select Department <span class="caret"></span> </button> <ul class="dropdown-menu scrollable-menu" aria-labelledby="dropdownCaseOwnerGroup"></ul> </div> JS getFieldData('Case Owner Group', function () { createFieldFilter('CaseOwnerGroup', 'Case Owner Group'); }, 'CaseOwnerGroup'); You see in this one I am passing 2 parameters. This is because the field name has spaces and it will cause issues. So the first parameter is the name we will be using through out our code and the second, is the one that we will use to communicate with Sense for data retrieval and data selection.Finally, let's alter the on click event that handles the selections on Line 108 to this: $('body').on( "click", "[data-select]", function() { var field = $(this).data('select'); var fieldName = $(this).data('fieldname'); var value = $(this).data('value'); app.field(fieldName).selectValues([value], true, false); $('#dropdown'+field + ' button').html(value + ' <span class="caret"></span>'); $('#dropdown'+field + ' ul li').removeClass('active'); $('#dropdown'+field + ' ul li:contains(\''+value+'\')').addClass('active'); }); If you want to use the 'ClearAll' button from the top toolbar then you will need to add in Line 72 under the case 'clearAll' : $('#dropdownCaseOwnerGroup button').html('Select Department <span class="caret"></span>'); $('#dropdownYear button').html('Select Year <span class="caret"></span>'); Our webpage should like this nowThat's it. Let me know if you run into any issues. I will be more than glad to help you with this!Yianni ...View More

Hello everyone, today I have the pleasure of introducing Jeff Goldberg as our guest blogger - (jog‌) - Jeff is a Senior Enterprise Architect on the Americas Pre-sales team who has worked in various technology positions for over 15 years. In between running half-marathons and crushing crossfit workouts , Jeff focuses on integration, deployment, automation, security, and api topics across a wide range of software. If you have a technical challenge, Jeff can put you on a path to figuring out the best way to overcome it. Jeff has the extreme pleasure of introducing you to SAML authentication with Qlik Sense, not only in text - but as well in supporting videos. Thanks for this valuable contribution Jeff!Qlik Sense SAMLWith the release of Qlik Sense 2.0, we introduced SAML as an authentication option between enterprise identity management systems (known as identity providers) and Qlik Sense. While SAML is a standard for authentication and authorization, it is open to interpretation and variability in its implementation. Consequently, implementing SAML can be a bit tricky. The goal of this blog post is to demystify SAML and provide some examples you can use to implement it with Qlik Sense.SAML stands for Security Assertion Markup Language, an xml based authentication and authorization standard for web applications to exchange user credentials and attributes. SAML works between two parties, an identity provider (IdP) and a service provider (SP), to facilitate single sign-on access to secure content for a user.Identity providers come in a variety of shapes and sizes. Salesforce.com, Active Directory Federation Services, and Ping Federate are just a few of the options available for handling the authentication components of the SAML handshake. The service provider is the system with the content we want to access. In this case, Qlik Sense is the service provider.The figure below illustrates the SAML authentication process.Keep in mind the SAML protocol is an open standard, therefore, implementing the solution between the SP and the IdP differs based on the requirements of the chosen provider.For example:ADFS metadata contains a whole section of information that needs to be removed before it is imported into Qlik Sense. With Ping Federate, the IdP configuration needs to have the name ID format for the SAML response manually set to transient or the user authentication to Qlik Sense will fail. One login doesn’t care about the name ID format for the response.Bottom line, expect some trial and error when configuring SAML, regardless if it’s with Qlik Sense or some other solution.To ease the pain and hopefully reduce frustration, we have created some videos to help walk through configuring different SAML identity providers with Qlik Sense. As we encounter more flavors of IdP, we will create content to help with configuration. For now, have a watch of the following videos, enjoy! Video Link : 3601 Video Link : 3605 Video Link : 3653 Video Link : 3652 ...View More

So you're interested in building custom charts in Qlik Sense mashups but don't know where to begin? Let's walk through building one.The basic flow of building a custom chart goes like this -1. Get a hypercube2. Format data as needed3. Draw or update chartSTEP 1 - GET A HYPERCUBEWe will use the Qlik Dev Hub to make creating our hypercube easier (information on the dev hub here). Open the dev hub and create a new mashup using whichever template you'd like (I chose the grid template). Now, choose an app from the dropdown near the top left of the edit window. Now, let's create the hypercube. Click the 'Add hypercube' button in the top right of the editor window, and a dialog will popup. Choose the dimension(s) and measure(s) you will need to create your chart. Set the rows to the number of rows of data you would like to include in your chart, and enter a name for a callback function. Then click 'Add'.Now a method to create a hypercube, and the callback function will have been added to your .js file. I chose to add a hypercube using the 'Month' field as a dimension and the 'Margin Amount' measure as my measure, using the Consumer_Sales app. If you want to follow along with me exactly, and recreate the "Margin Amount Over Time" chart from the Consumer_Sales app, you will have to manually edit the hypercube that has been generated a tiny bit. By default, a hypercube sorts the data by the load order of the first column, usually your first dimension. In order for months to be in the correct order in the data I will get back from my hypercube, I can sort the "Month" dimension numerically. The picture below shows the code I added to my hypercube, and you can check out all the properties that can be set on a hypercube here.STEP 2 - FORMAT DATAThe callback function you specified while creating the hypercube will be called when the data for the hypercube is returned from the engine, and every time the data in the hypercube changes. I like to log the reply as a first step, just to make sure my data is coming through correctly, and check out what it looks like.In the console, you can then check out the reply you get. Now, you'll want to figure out how you can format the data in the reply to use in creating your chart. Obviously, this is dependent on how you'll be creating your chart. For this example, I've chosen to use c3.js, but there's a good number of interesting and useful javascript charting libraries, any of which you can make use of.An example of a c3.js line chart can be found here. Now that I know what my data basically needs to look like, I can format it. Below is a screenshot of the code I used to format the data.STEP 3 - DRAW OR UPDATE THE CHARTNow that I have created a hypercube, and formatted the data, I need to actually draw the chart, and handle any time the data is updated. How you accomplish this can vary wildly. For instance, you could create a chart each time the data is loaded or changed. This would look something like creating the chart and appending it to a dom element, and every time data is updated, clear the dom element and recreate the chart and append it to the dom element again. I usually prefer to only create the chart once, and then just update the chart, if possible. With c3.js, this is very easy as it has a built in method for simply updating data on an existing chart. That means the only thing I really have to worry about is to check and see if the chart exists yet, and if it does not then create the chart, and if it does then just update the data. That looks like this -SUMMARYWe created a hypercube, and then in the callback function for the hypercube, which gets called every time the data in the hypercube loads or changes, we formatted the data into a usable format for our chart, and then either drew the chart or updated the chart based on whether it already existed. Read through again, check out the full project, attached, and also live at http://webapps.qlik.com/c3-chart-example/c3-charts.html and let me know if you have any questions. ...View More

For any BI tool to work it needs to understand who is using the tool. If we know who is using the tool (authentication) we also need to understand things about them such as where they work, e-mail address etc. If we know who the user is and what describes him/here we can do things like giving them access to the right information and tailor their experience. We therefore have methods in both QlikView and Qlik Sense to access information describing the users in user directories. Even though we have this capability in both QlikView and Qlik Sense, their approach to doing it is very different.The most common user directory used by our customers is LDAP. LDAP is a distributed database that is suitable for maintaining user information. It exists in both open solutions developed by the open source community (such as OpenLDAP) and in closed solutions (such as Microsoft Active Directory).So what are the differences between QlikView and Qlik Sense?QlikView is based on the concept with a directory service connector that fetch user information as it is needed. It doesn't replicate any data stored in the LDAP database, but instead asks a question every time it needs access to user information including groups.Qlik Sense uses a sync approach, which means that it replicates the user information including groups from LDAP into a database and then keeps this in sync. This can be done in different ways (sync all users, sync with a filter, sync only users that access Qlik Sense) depending on your needs and size of directory.There are two major usages of the user information, each with different needs: for management and for access control/authorization.For management it is good if the information is current but not absolutely critical, so here the Qlik Sense scheduler is used to update the data.For security however, it is critical that information is current. Therefore individual user records are updated as the user connects to the Qlik Sense environment.So, are there any similarities in how you configure Qlik Sense and QlikView?YesIn the picture below I describe the relations between QlikView LDAP configuration and Qlik Sense LDAP Configuration.I hope that you found these tips on how we access user information helpful. If you have questions on this blog post or have ideas of what you want to read about in the future, please don’t hesitate to add comments to post. ...View More

Do you even need to delete or keep some characters in a string field? The PurgeChar and KeepChar functions allow you to purge and keep characters that are in a string. The PurgeChar function takes two parameters. The first is the string and the second is the character(s) that are to be removed from the string. The KeepChar function also takes two parameters but in this case the second parameter is the character(s) that are to be kept in the string. Let’s take a look at some examples.Sometimes you may have a dataset that has garbage in it like in the FirstName field below.In this case there are characters after each name that I do not need. In order to remove these characters from the field, I can use the PurgeChar function in my script (see below) to remove all the unwanted characters from the FirstName field.Once I run the script the names look like this:The KeepChar function works similar except in this function you indicate what characters you would like to keep. This may be helpful when you have field that includes a mix of numbers and letters but you only want to keep the numbers or the letters. In this example, I have a ProductCode field that has codes that are made up of numbers and letters but I only want the numeric data.In my script, I can use the KeepPurge function and use the second parameter to list all the numbers since those are the characters I want to keep in the string.The end result looks like the image below. The product codes are now all numeric and the letters have been removed.There are a host of string functions that can be used to clean up or modify a string but when there are specific characters that you need to remove or keep, PurgeChar and KeepChar can be helpful and easy to add to your script or chart expression. These functions work well when you need to remove or keep ALL references to a character in a string.Thanks,Jennell ...View More

When building analytical applications you very often encounter cases where you want to accumulate numbers inside a chart. It could be that you want to calculate a Year-to-date number, or a rolling 6 month average, or a moving annual total. When creating an accumulation, there are two fundamental challenges. First, there is the question on how to treat dimensional values with no data. The tables below illustrate the problem: In this example, there is no data for the month of March. The default behaviour is that this row then is omitted, see left table. This leads to an incorrect accumulation – the rolling 3 total for April includes January, which it shouldn’t. The table to the right however treats missing months correctly. Secondly, there is the question on how to treat the first dimension when there are several dimensions. The tables below illustrate the problem: In the left table, you want the accumulation to re-start for each new product. You do not want the accumulation to continue from the previous product. Note that for ‘2014 Jan’ the accumulation is reset. But in the right table, it is the other way around: A moving annual total should span over two calendar years, so you want the accumulation to continue over into a new value of the first dimension. In QlikView and Qlik Sense there are several ways to create accumulations, each with its own pros and cons. 1. Using the control in QlikView charts In a QlikView chart, you can easily create an accumulation just by selecting the right chart setting: This method will however fail both the above challenges. Also, an accumulation can only be made over the first dimension, and the accumulation will always be reset for a new value in the second dimension. Further, you cannot use it to calculate moving averages. It only works for calculations of sums. 2. Using the Above() function If you use the Above() function in a chart table like RangeSum(Above(total Sum(Amount) , 0, 12)) or in an Aggr() table like Only(Aggr(RangeSum(Above(total Sum({Amount),0,12)), YearMonth)) you will get a 12-month moving total. It will pass the “multiple dimensions” challenge: By using – or not using – the total specifier in the Above() function you can decide which behaviour you want. But it will fail the “no data” challenge. There is a however a way to make an accumulation in an Aggr() table work also for this problem: Trick the QIX engine to show all rows by adding a zero term for all dimensional values: Only(Aggr(RangeSum(Above(totalSum(Amount) + Sum({1} 0),0,12)), YearMonth)) and enable the display of zero values. Then you will get the right numbers also when some dimensional values are excluded. 3. Using an As-Of table in the data model The As-Of table, is an excellent solution that will pass both challenges. Here you must set the condition using Set Analysis: Sum({$<MonthDiff={"<12"}>} Amount) However, it has one drawback: When a user clicks in the chart, a selection will be made in the AsOfMonth. But you don’t want the user to make selections here: You want month selections to be made in the real month field. So I usually make my charts read-only if they use the AsOfMonth as dimension. With this, I hope that you understand accumulations better. HIC Further reading related to this topic: The Above Function The As-Of Table Calculating rolling n-period totals, averages or other aggregations ...View More

In many of my webpages that I've created using the Capabilities API, I had to create a custom menu and I always used Bootstrap's Dropdown component. In this tutorial I will show how to do the same.From the Mashup Editor, create a new project and give it a name, using "Grid Mashup Template". this will include bootstrap for you..Select the app "Helpdesk Management.qvf".From the right hand side, create a list and select "Department" from the Dimensions. Make sure you add a callback function, since that is where we will put our code. Here, I named it showData.Now, lets go to the html template and add the dropdown code.In the div that has the id="QV01", add the following: <div class="col-sm-4 qvplaceholder" id="QV01"> <div class="dropdown"> <button class="btn btn-default dropdown-toggle" type="button" id="dropdownMenu1" data-toggle="dropdown" aria-haspopup="true" aria-expanded="true"> Select Department <span class="caret"></span> </button> <ul class="dropdown-menu" aria-labelledby="dropdownMenu1"> </ul> </div> </div> In your javascript code, you will see a callback function created. //callbacks -- inserted here -- function showData(reply, app){} The reply has all of the data from the Dimension so we can create the list elements in the drop down. So, let's populate the drop down list elements. function showData(reply, app){ $('#QV01 .dropdown ul').empty() $.each(reply.qListObject.qDataPages[0].qMatrix, function(key, value) { if (typeof value[0].qText !== 'undefined') { $('#QV01 .dropdown ul').append('<li><a data-select="'+ value[0].qText+'" href="#">'+ value[0].qText+'</a></li>'); } }); } Notice that we empty the list first, otherwise every time we make a selection, the same elements will be added in the drop down.Now, we have to add a jQuery on click event that will trigger the selection and change the button value. $('body').on( "click", "[data-select]", function() { var value = $(this).data('select'); app.field('Case Owner Group').selectValues([value], false, false); $('#QV01 .dropdown button').html(value + ' <span class="caret"></span>'); }); The selection drop down is ready. now lets add some more graphs to make it more interesting when we select a department. I added 'Open & resolved cases over time', the KPI 'High priority Cases' and 'Case Details'. So the final page should look like this:Last, we must set the button to the original state after the user clicks on the "Clear All". We just add the following line in the code. case 'clearAll': app.clearAll(); $('#QV01 .dropdown button').html('Select Department <span class="caret"></span>'); break; ...View More

Last week I wrote about how the Above() function can be used for calculating rolling averages and other accumulations. There is however also an alternative method for doing the same thing:The As-Of table.When you use the Above() function, you fetch a number from other rows in a chart or Aggr() table. The As-Of table is slightly different in this respect: It is not a transient table created by an object or an expression – instead it is a real table in the data model.The idea is to create a secondary month field – the AsOfMonth - that links to multiple real months.In the example above, you can see that ‘2015 Oct’ links to several preceding months, and each Month in turn links to several rows in a fact table. This means that a specific transaction will be linked to several AsOfMonths.In the data model, the As-Of table should appear as a separate calendar table that links to the existing primary calendar table:One way to create this table is the following:First, make sure that you in your master calendar have a field “Month” that is defined as the first date of the month, e.g.Date(MonthStart(Date),'YYYY MMM') as Month,Then add the following lines at the end of the script:// ======== Create a list of distinct Months ========tmpAsOfCalendar:Load distinct Month Resident [Master Calendar] ;// ======== Cartesian product with itself ========Join (tmpAsOfCalendar)Load Month as AsOfMonth Resident tmpAsOfCalendar ;// ======== Reload, filter and calculate additional fields ========[As-Of Calendar]:Load Month, AsOfMonth, Round((AsOfMonth-Month)*12/365.2425) as MonthDiff, Year(AsOfMonth)-Year(Month) as YearDiff Resident tmpAsOfCalendar Where AsOfMonth >= Month;Drop Table tmpAsOfCalendar;Once this table has been created, you can use the AsOfMonth as dimension in charts where you want rolling averages and accumulations.If you as measure useSum({$<YearDiff={0}>} Sales)you will get a yearly accumulation – year-to-date up until the day of the script run.If you instead useSum({$<MonthDiff={"<6"}>} Sales) / Count(distinct {$<MonthDiff={"<6"}>} Month)you will get a 6-month rolling average:And finally, if you useSum({$<MonthDiff={0}>} Sales)You will get the real, non-accumulated numbers.I have made the Set Analysis expressions based on two fields: YearDiff and MonthDiff. However, for clarity it could be a good idea to add flags in the As-Of table, so that the Set Analysis expressions become even simpler, e.g.If(MonthDiff=0,1,0) as IsSameMonth,If(YearDiff=0,1,0) as IsSameYear,If(MonthDiff<6,1,0) as IsRolling6,Summary: The As-Of table is a good way to calculate rolling averages and accumulations.HICFurther reading related to this topic:The Above FunctionAccumulationsCalculating rolling n-period totals, averages or other aggregations ...View More