If there has ever been a more “You’ve got your chocolate in my peanut butter!” moment, it’s the blending of Conditional Formatting with the Subtotals tool in Excel. If you have ever used the Subtotals tool to group information you have probable been impressed with its ability to group data by some changing event (like States) and have those groups aggregated and then structured into a collapsible outline. Before Subtotals After Subtotals But the one shortfall when it comes to the Subtotals tool is that there are no built-in artistic styles that can be applied to give the list a bit [...]
About adminThis author has not yet filled in any details.
So far admin has created 117 blog entries.
The goal of every QlikView developer is to do as little work as possible. When you notice that you are writing the same instructions in multiple app scripts, the notion of re-use is a red flag that flies with great fervor. QlikView’s Include statement provide just such an ability. The advantages of re-using scripts between multiple apps has several advantages: Complex scripts can be used by multiple people with superficial knowledge of the script’s logic. Include files ensure business process consistency when performing similar actions across multiple apps. Include files expedite script maintenance because the script need only be updated once [...]
One of my pet peeves (wouldn’t that be a great name for a dog; “Peeve”?) when working with charts in QlikView is that I have a tendency to click on bars accidentally when showing data to colleagues. When I do, all of the bars disappear except for the bar I mistakenly clicked on. And for that millisecond in time, everyone receives a mental jolt that I wish would rather not have happened. Since I was trying to direct their attention to a particular month, I think a better behavior would have been for the bar to change color to visually separate [...]
Excel’s MODE function is a great tool for returning the most frequently occurring number in a set of numbers. But what if you want to return the most frequently occurring word in a list of words? MODE with Numbers Using the MODE function in Excel is quite simple; you point to a list of numbers and MODE will tell you which number occurs the most often. In this list, the number “4” appears more often than any other number. MODE with Words As you can see, the MODE function does not work very well when pointing to a list of words. [...]
The data transformation tools in QlikView are amazing. When I think of the hours I have spent working with data that was output by some system trying to get that data into a format that I could then START modeling. Oh, the wasted man hours. One of the QlikView transformation tools has the ability to take a crosstable (like a pivot table in Excel) and convert it into a straight table; a traditional flat table where data typically starts. The only problem with this tool is that it converts all of the data in the crosstable into straight text. This means [...]
Crystal Reports – Clear Recent Reports History As with most applications, Crystal Reports maintains a history of the last several reports opened so as to facilitate repeated access. This is a very helpful feature, but there comes a time in every report user’s life when they just want to clear the list and start clean. Many applications contain a mechanism that allows the user to clear either individual entries or empty the entire list. Sadly, Crystal Reports contains no such feature. […]
Excel – Mixed Pivot Table Layout Microsoft Excel Pivot Tables is one of the greatest inventions known to man; second only to those buttery mints they bring you in fancy restaurants. Even the greatest of ideas are not without their issues. Take for example the Report Layout feature in Pivot Tables. Excel gives the user three options with which to display hierarchically (wow; that’s a hard word to spell) related data. Compact Form Outline Form Tabular Form Let’s examine what makes each layout unique. Compact Form All row-based data is combined into a single column, one row per line item, and [...]
Converting Names into Email Addresses Suppose you have a list of names, perhaps a roster of employee names, and you wish to generate email addresses for these individuals. If you work at a company that has an established standard for email addresses (i.e. first initial of first name with last name) then you have a few options. The preferred strategy depends largely on the version of Excel you are using as well as the naming pattern used in the emails addresses. Flash Fill (Excel 2013 / Excel 2016) If you are not familiar with Flash Fill, this tool allows you to [...]
In our recent free webinar, SkillForge instructor Bryon Smedley shows all of the cool things you can do with Pivot Tables in Microsoft Excel. If you're not using them already, you can learn all about them in our Microsoft Excel Pivot Table Training Class! https://youtu.be/KcYSI1cccoc
https://youtu.be/A_YfJm1yttU In this webinar, you'll see how to create dashboards and data visualizations with data from Excel and other data sources using QlikView. For more on our QlikView Training Courses visit our QlikView Training page.
https://youtu.be/u3QSZYOhMVk In this tutorial, you'll see how to round the duration of a task in Microsoft Project by using a custom field. You'll also see how to use this custom field in all of your projects by adding it to your Global.mpt file.
Dependency Types in Microsoft Project When creating schedules in Microsoft Project the first thing that a Project Manager would typically do is to input the tasks involved in a project. These tasks then need to be linked to show the relationship between them. These links create task dependencies. There are 4 different types of task dependency: Finish-to-Start (FS): The finish date of one task drives the start date of another. Start-to-Start (SS): The start date of one task drives the start date of another. Finish-to-Finish (FF): The finish date of one task drives the finish date of another. Start-to-Finish (SF): The [...]
If you’ve ever used Pivot Tables in Excel, you no doubt have discovered the wonders of filtering. The ability to filter row or column items can be extremely helpful when you don’t wish to analyze all of the items in the driving data set. But what do you do if you wish to filter by the Value-based items? In other words, the numbers in the “connect the dots” area where row and column choices intersect. These, on first glance, don’t appear to have sorting and filtering controls available. Rest assured, they do exist; you just have to dig a bit to [...]
VLOOKUP is great for returning information from a database, but one of the limitations is that the return information is static. What if the user wishes to look for certain data one day but different data another day? This would require either two different sets of VLOOKUP functions or the functions would need to be reprogrammed. In the database below, the user would wish to return address information in one scenario, but return financial information in another scenario. Suppose there are times when the user requires a mixture of the two; that would require a third set of VLOOKUP functions. This could [...]
Excel PivotTables are one of the greatest tools in the spreadsheet user’s toolkit. However, there is one tiny bit of functionality that appears to be missing: the ability of pivot tables to automatically update when information in the source data changes. Most user’s see this as a glaring lack of functionality. There is, however a very good reason why pivot tables do not automatically update. […]
With so many hardware devices (like printers, external hard drive enclosures, and home network devices) equipped with USB ports, it’s likely that you’ve collected an array of advertised drives in Windows Explorer that are not actually accessible unless something is connected to them. Perhaps you have setup a mirrored drive for backup purposes that you want to be “out of sight; out of mind”. Hiding these devices is quite simple… provided you can do a bit of counting in binary. […]
Selecting a font in most applications has come a long way over the years. In the old days you had to select each font separately from a drop down and decide if it was the right font for the job. Then along came the ability to sample fonts by hovering your mouse pointer over the list. What a time saver. But not in Photoshop! (Pssst! You actually can, but don’t tell anyone. It’ll be our little secret.) […]
Admit it; you’ve done this more than once. You open an Office application like Word or Excel and type out your next great novel or number-crunching masterpiece. Then, in a state of haste, you start closing windows and accidentally close the one window you intended to leave open. One second later you realize, this was the one windows you shouldn’t have closed. All is lost… or is it? […]
If you have worked with QlikView and encountered the error message “Allocated Memory Exceeded”, there could be numerous reasons for this message. You may have a quite a quest in store for yourself to figure out what the issue is. But there’s quick and easy thing to check that may just be the reason for your plight. If you have been writing expressions, one of the primary culplits for generating this memory error is a simple syntax error with your expression. Here’s just such a scenerio: You have created a fantastic pie chart and wish to add some intelligence to the [...]
If you scan documents from a network scanner (like the giant printer down the hall) and have the scan sent to your email, you are probably less than thrilled with the name that the scanner gives the file. The name is usually something generic and uninformative like “Scan0001.pdf” or a combination of the date and the time like “07182015-100855.pdf”. […]
This tutorial applies to PowerPoint 2013, but several earlier versions of PowerPoint possess the same abilities. The exact steps and options will vary slightly from version to version. Showing your PowerPoint masterpiece can be thrilling, but what if you want to reach a wider audience? How can we take our presentation from the confines of the boardroom (or is that bored room?) and set it free so the world can bask in its glory? Very easily; we convert it into a video. In this tutorial, we discover a feature introduced in PowerPoint 2010 which allows you to take your slides and [...]
Have you ever been looking at your scripts in QlikView and thought to yourself, “What do all these different colors represent?” Well, puzzle yourself no further. I am here to explain what all of these colors are trying to say and then show you how to change them if you have your own color preferences. […]
Admit it, you’ve used Excel functions without any idea how they work. Someone said, “Click here for this. Click here for that.” You’ve dutifully followed orders, blindly clicking on cells with no real clue as to why. It’s time to lift the veil of mystery and understand why these functions are so demanding of such data. Here is just one of many options for understanding function logic: This first part has nothing to do with obtaining function information, but it’s a great time saving trick. If you like to type your functions directly in the cell; press your EQUAL button [=] [...]
Let’s set the stage: You receive a file from your co-worker where data has been arranged into multiple columns to create a table-like format. Upon careful examination, you notice that the column alignment is just a little bit off. To discover the cause you press your Show /Hide button in Word to reveal the formatting codes. […]
If you are new to Excel, you no doubt have already discovered the need to take a series of numbers created by formulas and convert them to fixed values. In other words, replace the questions with the answers. With no idea how to accomplish this, beginners usually spend great stretches of time retyping the numbers into the answer cells to make them “permanent”. Eventually, someone who feels your pain turns you on to the technique of highlighting all of your formula cells, clicking Copy, then in the same cells clicking Paste Special…, Paste Link. You could now not be more ecstatic. [...]
If you have ever inserted an object (like a picture, shape, or clipart) into a Word document and then made the unfortunate move where you pushed it behind your text... ...and now it seems forever trapped behind a sea of impenetrable words. No matter how hard you try to click, you just cant seem to select that image again. That image now appears forever disconnected; never again shall it be cropped, moved, or resized. Don't fall into the trap of most users where you end up deleting all of that valuable text just to get to the image that hides behind. [...]
If you are an Excel user who LOVES working from the keyboard as much as possible (i.e. data entry, navigation, feature activation, etc...) then you simply MUST know of this little gem of a keyboard shortcut. Although we have no scientific data to back up this claim, our hunch is that the COMMA STYLE is the most popular style in the Number Styles library. To save time when applying the COMMA STYLE to cells, use the keyboard shortcut CTRL-SHIFT-1 This will apply the COMMA STYLE to the selected cells. If you want to experiment (what we call "having fun"), try the [...]
If you work with Microsoft Project and have ever leveraged the power of Resource Pools across multiple projects, you may have encountered a strange behavior when assigning those resources. When you share resources between a Resource Pool file (a dummy project file that typically has no tasks but is merely a container for holding resources) and another project file, sometimes the resources don’t show up when it comes time to make the resource assignments. […]
http://youtu.be/AHJIcqSFhzQ In this Microsoft Excel PowerPivot tutorial, you'll see how to use Microsoft Excel PowerPivot to import data from multiple data sources, link the data based on common fields and finally create a PivotTable and PivotChart to quickly analyze the data. This content is from our live, instructor-led Microsoft PowerPivot Training class.