Category Archives: Productivity

Reducing Qlik App Resources with QSDA

Longish post today where I show off the power of QSDA Pro to reduce Qlik App resources, saving you money and creating a more responsive experience for your users.

As a follow on to my last post “Help! My Qlik Cloud Reload Exceeds Quota!” I’m going to dive deeper into the available QSDA features to reduce memory requirements of a Qlik App. As a bonus we’ll use QSDA to improve the performance of a chart.

There are many good reasons to optimize your Qlik Apps to a leaner size. Smaller apps tend to perform better and place less demand on your server resources. If you are a Qlik Cloud customer, or planning a move to Qlik Cloud, App memory size impacts your license tier — and therefore your direct monthly costs.

In this post our starting point is I that have identified the App “BikeShop Sales” as having a large (597 MB) footprint. I got this information from Qlik App Metadata Analyzer as shown below.

Qlik App Metadata Analyzer

I could also have identified this memory footprint in QSDA Pro when we select this App from the list.

QSDA Applist

Let’s see how QSDA Pro can help. I’ll launch a new Analysis and give it the Description “Baseline”.

When the analysis completes press “View” to see the results.

QSDA Analysis Summary page

In the Resources card I see that unused fields occupy 383 MB — almost 2/3 of the base data model! Unused fields are fields that are not used anywhere in the front end. They can be removed without impacting the contents of charts or the user experience.

I also see some other suggested savings in dropping unused tables (“All fields unused but keys — 439 KB”) and a 530 KB savings by AutoNumbering keys.

A fast and simple way to remove unused fields is to drop them at the end of the script with a “DROP Fields …” statement. AutoNumbering is also best done at the end of the script. Let’s implement the QSDA recommendations. Open the Qlik Data Load Editor for “BikeShop Sales” in another browser tab.

The QSDA Tools menu provides a handy Script Generator to generate recommended script statements. Select the script generator from the Tools menu.

QSDA Tools menu

In the Script Generator > AutoNumber tab select the top checkbox to select all recommended fields. Press the “Copy” button to copy the generated AutoNumber statement to your clipboard. Paste the statements at the end of your Qlik Script. Repeat for the Drop Fields and Drop Tables tabs. (I won’t use the Load Statement tab just yet).

Now that I have updated the script with the AutoNumber and Drop statements, Reload.

Reload with AutoNumber, Drop Fields, Drop Tables

Returning to the QSDA App Card, I can see that Base App Memory is now 213 MB. Quite the savings from 507 MB!

App card after reload

At this stage, I recommend running a new analysis to confirm that the script changes have not introduced any problems or new issues.

Note that Reload Peak Memory is unchanged, because we loaded the unused data before we dropped it. Reload memory has an impact on the performance of on-prem servers and for Qlik Cloud, reload memory is a direct cost.

Can we use less reload memory by not loading the unused fields and tables? It may be that a field is used the load script but is not used in the front end, so you have to examine your script to determine. This very clean script loads directly from QVDs so the answer is clear.

I’ll start by commenting (or removing) the tables we don’t need instead of dropping them at script end. To identify the unused tables I can refer to the previously generated script, the Flags, or return to the Script Generator tool in the Baseline analysis.

What about Tables with both used and unused fields? Can I easily load just the used fields and omit the unused fields? QSDA Script Generator to the rescue again. Open the Script Generator > Load Statement in the Baseline analysis and sort on the “Unused Bytes” column. Select the SalesOrderDetail table.

A Load statement fieldlist for [SalesOrderDetail] will be created with unused fields commented out. You can exclude the unused fields entirely by unchecking the “Include Unused Fields” box. I’ll start with just the SalesOrderDetail table for now. Note that the other tables with big Unused Fields savings, Notes and Customers, are already being excluded completely.

Before reloading, I’ll remove the AutoNumber and Drop statements I added earlier. Many of those drops are no longer valid as I am not loading the fields at all. Here is the updated load script.

Updated script to exclude unused fields

After reloading, I return to the QSDA App Card. Reload Mem is reduced from 693 MB to 268 MB! I’ll run another analysis to see where things stand.

I can address the remaining unused fields with DROP Fields or using the Load Statement method. I will add the AutoNumber statements back in as well. I hope the process is clear enough now so I won’t show these remaining steps in detail here.

Let’s turn our attention to another great value of QSDA — Performance improvement. The “Calc Time Distribution” chart plots the calculation time of each chart in the App. I see I have an outlier that takes about 4 seconds to calculate.

What is this chart and why is is taking to long to calculate? Flip over to the Viz page and sort by the Calc Time column. The viz in question is a Table named “Salesperson Scorecard” on sheet “Dashboard” and it takes 4.142 seconds to calculate. Expanding the row shows the chart Dimensions and Measures and also exposes the “Viz Deconstruction” icon

Viz Details

Clicking the Viz Deconstruct icon will calculate each chart Measure in turn and tell us what is taking so long to calc in this chart. We can see the total calc time is over four seconds and measures three and four take considerably longer than the first two. But why?

Let’s focus on this chart to get some insight into the cause and solution of the poor performance. Back on the Viz page, Right-click the Viz of interest and select “Filter to Selected”. This will filter our pages to only those objects — Dimensions, Expressions, etc — associated with this Viz.

Click over to the Expressions page and we’ll see that our long running expressions have flags. Flags are the advice system of QSDA, identifying errors, bad practices and performance issues. Our two second expression “Longest Deal” has a “Key Field used in Expression” flag.

Clicking the “i” icon next to the flag will open the Flag Details panel which contains a link to the help article for this flag.

In the help article we’re informed that using a key field in an expression can sometimes cause poor performance. The article goes on to advise using a non-key field instead, creating a new field if necessary. I’ll take this advice and create a new field in the SalesOrderHeader table:

Reload and change the chart Measure to use the new field.

  Max(Aggr(Sum(LineSales), SalesOrderRecord))

Run a new analysis to check the calc time of the chart (and check we didn’t impact anything else!). In the summary we’ll see a significant decrease in total app calc time and we can drill into our chart to see what’s changed.

Whoa! That measure has gone from 2 seconds to 0.008 seconds.

But Rob, didn’t I just blow up my RAM by making a copy of this big field? Don’t guess, use QSDA to see exactly what the cost is.

Additional RAM of SalesOrderRecord field

The cost is 50 KB of RAM and no significant increase in Reload Mem. Was it worth it? I think so.

If you chose to put the new field on the SalesOrderDetail table instead of SalesOrderHeader, the QSDA results will quickly show you that this was a bad idea. RAM is much increased and performance not so good.

QSDA Pro gives you the tools to:

  • Reduce your App memory and compute requirements.
  • Focused advice and insight for improving your App.
  • Compare different solutions and measure the impact of change.
  • Track your progress and document the impact of your work.

I hope this post makes it clear why I say that QSDA Pro is “the tool that every Qlik developer needs”.

Do you have questions about QSDA Pro or want to schedule a demo for your team? Reach out to us.

If you’re attending Qlik Connect in Orlando, stop by the Motio booth on the show floor. I’ll be there (guy in the hat) to answer your questions and show you even more features of QSDA Pro that can help you create leaner, cleaner and faster Qlik Apps.

-Rob

Share

Help! My Qlik Cloud Reload Exceeds Quota!

If you are a Qlik Cloud (SaaS) customer you may have seen this dreaded message during script reload. Ack! Ack!

Your quota may be different than 25Mb, but the problem remains the same. How do I modify this application to fit within my subscribed quota?

QSDA Pro V3.1 to the the rescue! QSDA analyzes your app and provides recommendations and easy to use tooling to drop unused data and optimize the remaining data, potentially greatly reducing the size of your Qlik App.

QSDA needs a successfully loaded app for analysis, so our first step is to reload the app using the “Limited load” feature of the Qlik script debugger.

Click the Debug button in the Qlik script editor, check the Limited load option, accept the default of 100 rows and click “Run” to initiate the reload. The reload will come in under your quota. When the reload completes, key Ctrl-s to persist the data.

You don’t have a business-ready app, but this is enough for QSDA to scan all charts, expressions and master items to determine what data is actually required.

In QSDA Pro we now select our Cloud connection and select the app of interest, in this case “BikeShop Sales2”. Click the “Open” button to prepare the app for analysis. I will also uncheck the “Collect Calctime” option because chart calculation times are not meaningful with our limited data. Description is optional, in this case I’ve entered “Limited Load”.

Click the “New Analysis” button to launch the QSDA Pro analysis. The analysis may take a minute or two to complete depending on the capacity of your tenant and the current Qlik Cloud load. When the analysis is complete press the “View” button to see the results.

In the Summary view we see there are 89 unused fields. Unused fields are fields that are not used in any charts, master items or expressions. These are fields that can be dropped at the end of script without impacting the user experience in the app. The actual storage numbers (KiB) are not meaningful because we only loaded 100 rows. The bar chart is useful in that is indicates about 2/3 of our data is unused. Hope!

QSDA provides a Script Generator in the Tools menu to generate a “DROP Fields …” script statement for unused fields. Select the script generator from the Tools menu.

In the Script Generator > Drop Fields tab select the top checkbox to select all recommended fields. Press the “Copy” button to copy the generated Drop Fields statement to your clipboard.

Paste the Drop Fields statement at the end of your Qlik Script and reload.

Reload successful! Victory!

A subsequent QSDA analysis shows the app size has been reduced from the problematic 37Mb to 15MB!

QSDA Pro can quickly and efficiently get your Qlik Apps below your Qlik Cloud quota by pruning unnecessary data. Don’t pay for more than you need.

In a follow up post I’ll walk through some additional QSDA features to help you reduce Qlik app footprint even further and improve chart response time to boot.

Download QSDA Pro and try it for yourself. Or reach out to us to learn more about license options or arrange a demo or POC for your team.

-Rob

Are you going to Qlik Connect? I’ll be at the Motio booth on the show floor ready to demo QSDA Pro or answer any questions you may have. Or just to say Hej 🙂

Share

Formatting Intervals

In this post I’m going to show some tips for formatting intervals / durations in Qlik.

Datetimes in Qlik utilize a serial number that equals the number of days since December 30, 1899. The integer value represents the day and the fractional value represents the time of the day. The difference between two datetimes is an interval or duration.

Qlik provides the Interval function to format numeric interval values as dual strings for display. Interval() may be used both in charts and script. The Interval function accepts an optional format string that specifies the output format.

An example interval numeric value could be 3.3414351851898, three days and a few hours. The expression

Interval(3.3844097222245, 'hh:mm:ss')

will yield “81:13:33”. Note the 81 hours representing 3 days and 9 hours. We can exceed 24 hours because interval accumulates overflow in the largest unit. For example, we could show the duration as minutes using

Interval(3.3844097222245, 'm') 

yields “4873”

We can add the format character “D” (or “d”) to indicate days.

Interval(3.3844097222245, 'D hh:mm:ss')

will yield “3 09:13:33”.

Is it clear what this number string represents? The “:” may allow a user to intuit the hh:mm:ss. But what about the “3”? Can we make this clearer by adding some additional text to the output? (From now on I’ll use a field named “Duration” in my examples). Will this work?

Interval(Duration, 'D days hh:mm:ss')

No. We will get some funny looking output because the “d” and “s” in “days” will be interpreted as format characters.

The format pattern does not have a syntax to declare “this is a literal, not a format character”. We can work around this limitation by using a non-format character as a proxy and patching it up with a Replace function. I’ll use “a” to represent the word “days “

Replace(Interval(Duration, 'd a, hh:mm:ss'), 'a', 'days')

You may have noticed that the values are now left justified. This is because Replace() returns a string, whereas Interval() returns a Dual value. We probably want to retain the Dual nature to allow for proper sorting. I’ll fix it by adding a Dual() function.

Dual(Replace(Interval(Duration, ‘d z, hh:mm:ss’), ‘z’, ‘days’), Duration)

How about if I want to show only days and hours?

The format string “d h” will show days and hours. Minutes and seconds will not display. However…the internal numeric value will remain unchanged. Generally not a problem, but may cause some duplicate lines if the value is used as a Dimension or filter. Because of this, I like to round the value to match my display.

Interval(Round(Duration, MakeTime(1)), 'd hh')

MakeTime(1) is the value of one hour. If you want to round to 4 hour segments use MakeTime(4). Instead of Round() you can also use Floor() or Ceil(). For example, to make 15 minute buckets use Floor(Duration, MakeTime(0,15))

Let’s put Round and Replace together to display a clearly formatted days and hours. I’ll use “b” as proxy for “hours”.

Dual(
  Replace(
  Replace(
   Interval(Round(Duration,MakeTime(1)), 'd a, h b')
  ,'a', 'days')
  ,'b', 'hours')
, Duration)

Almost there. I don’t like the “0 days”. How about we don’t display days if zero? Let’s make “days” conditional on Duration being >= 1.

Dual(
  Replace(
  Replace(
    Interval(Round(Duration,MakeTime(1)),
      if(Duration >= 1, 'd a, ', '') & 'h b'.)
  ,  'a', 'days')
  ,  'b', 'hours')
, Duration)

I like it. It has become a bit wordy. I may want to use it elsewhere in this app (or others), perhaps for a different field. I’ll make it reusable by assigning the expression to a Variable with Parameter. Add this statement to the load script.

Set FormatDuration = Dual(
  Replace(
  Replace(
    Interval(Round($1,MakeTime(1)), if($1 >= 1, 'd a, ', '') & 'h b')
  , 'a', 'days')
  , 'b', 'hours')
, $1);

“$1” is a placeholder for the fieldname. The chart expression is now simply:

$(FormatDuration(Duration))

Looking for more tips & tricks? Have some of your own to share? Join us in September at the Masters Summit for Qlik in Vienna. In addition to our focused hands-on workshops on migrating, managing and developing in Qlik Cloud, we’ll be sharing lots of tips and techniques gleaned from years of Qlik experience.

-Rob

Share

QSDA Pro 2.5 Brings Extended Expression Validation

Summary: Set Modifier field names and Set Identifiers are not validated by Qlik syntax check. QSDA Pro 2.5 validates everything and raises an “Unrecognized Name” flag for invalid names.

QSDA Pro syntax checks all expressions in a Qlik App using the Qlik syntax checker. You’re familiar with the Qlik syntax checker. It’s the message at the bottom of the expression editor that tells you your expression is “OK”.

The syntax checker is also good at telling you when the expression has an error, although it’s not always specific about the problem.

QSDA Pro, using the Qlik API version of syntax checker, tells you what is wrong with the expression:

The Qlik syntax checker has a significant limitation. It does not validate field names used in sets. Whether used in the API or the editor, the syntax checker will not raise an error if the field “EmpPurchase” does not exist in the data model.

This lack of validation can be a serious problem if “EmpPurchase” is removed or renamed in the data model. The expression will still return a number but the exclusion of employee purchases will no longer be applied. That could be a very subtle but important fail.

Recognizing this limitation and it’s potential impact, QSDA Pro 2.5 validates all names used in sets and will raise a new Quality flag, “Unrecognized Name”, if the name is invalid.

Another place this validation is extremely useful is Chart Filters in Qlik SaaS. The chart will cheerily display that filter “Expression2 > 1” is being applied. Even though field “Expression2” no longer exists in the data model.

But QSDA knows.

Ever use a bookmark as a set identifier? And then the bookmark got deleted or renamed?

I’ve used simple examples to demonstrate, but where this validation really shines is in longer expressions where it may be easier to overlook that something has gone wrong.

Yes, you need QSDA Pro. We all need QSDA Pro.

Learn more about QSDA Pro and download your own copy at https://easyqlik.com/qsda/

-Rob

What does QSDA stand for? Qlik Sense Document Analyzer. The combination of data model and sheets we know as an “App” in Qlik Sense was called a “Document” in QlikView. When I first created this tool in 2009 to help maintain QlikView, I called it “Document Analyzer”. When it came time to create a similar tool for Qlik Sense, I stuck with the “DA” name as the function and usefulness of “Document Analyzer” was well established in the Qlik community.

Share

Motio Acquires QSDA Pro

I’m pleased to announce that I’ve joined forces with Motio, Inc, producers of the great DevOps tools Soterre and Gitoqlok. Motio has acquired my QSDA Pro product and I’m excited to combine our tools to provide a comprehensive platform for professional Qlik App development in Qlik Sense and Qlik SaaS. You can read the announcement here.

I will continue to lead the development of QSDA Pro, enhancing the product and bringing new superpowers to my customers in the integration with Soterre and Gitoqlok.

The QSDA Pro customer base is growing fast! To date hundreds of organizations have purchased QSDA Pro and are realizing the full benefits of the tool. More are joining every day and I’m excited to add Motio’s dedicated product support and admin teams to handle this growth.

You can continue to learn more and purchase QSDA Pro online .

Share

Gitoqlok for Qlik Sense Version Control

Gitoqlok is a free chrome plugin that allows you to use a git repository such as GitHub, GitLab, Azure DevOps, etc, to manage all the pieces of Qlik Sense app — Measures, Sheets, Charts, Load script and more.

The plugin operates seamlessly within the Qlik Sense authoring experience to provide the power of fine grained version control you expect with git. You’ll see a list of changes and commit those changes as you would expect in any software development project. Of course there is collision detection, branching and merging and all the goodness you expect from git.

Because the Gitoqlok team understands the visual nature of Qlik development, you can view diffs in a visual format. A slider lets you pick a commit for compare.

You can also use the same slider in a non-diff “time machine” mode to see how the application sheets looked at any point in time.

Gitoqlok comes with a lot of very useful Qlik dev features, such as the ability to import a chart or script snippet from app to another. Or deploy an entire app from one server to another!

Most Gitoqlok features are free to use. Premium features and support are available in a reasonably priced subscription.

If you haven’t tried Gitoqlok I recommend you give it a spin. You can install the plugin from the Chrome Store. Learn more about Gitoqlok including some great intro videos.

If you are attending QlikWorld next week Drop by the Motio booth (#315) to see more. Motio is also presenting two breakout sessions showcasing Gitoqlok, including their integration with my QSDA Pro tool.

-Rob

Share

The New Qlik Simplified Authoring Mode

Qlik has implemented a new “Simplified Authoring” experience in the SaaS (Cloud) environment. It’s available as an optional preview now and will be implemented for all SaaS tenants in mid-August.

I won’t describe the new features as they are well described here. If you haven’t already, I recommend you read the post and then come back here for my comments.

I think this is really great work and a significant improvement to the chart authoring experience. It not only makes it easier for newbies to create charts, I prefer using this interface in most cases. What do I like about it?

Moving the data panel to the left and increasing the display density is much more productive and fluid. This change seems to signal the death of the “iPad first” design. I’m pretty happy about that. The ability to work with field values while in design mode is fantastic.

The addition of the Data Table is brilliant. It’s much more fluid than swapping tabs to use the Data Model Viewer or building a temp table on the canvas.

I didn’t think I would like the “smart grid” feature but I’ve become a fan. With additional elements like Data Table the working canvas becomes relatively smaller and I’ve found it simplest to just add my chart in the general location and move on. As a final step I can switch to advanced mode and fine tune the chart locations and sizes if necessary.

Very cool feature to me is the new Chart Filters. This filter is implemented as a set expression applied to the data before expression set analysis is applied. So you now have three layers of data filtering — User Selections, Chart Filter, Expression (Measure) set analysis.

The chart filter is requested by a set expression in hypercube property qHyperCubeDef.qContextSetExpression . So you can use it in the Engine API if the engine level supports it. Potentially we could see Chart Filters available in a future QSEoW release. I think it would be useful to have Chart Filters as a master item for reusability and consistency.

If you are using QSDA Pro to evaluate your Qlik Sense apps (why not!), QSDA Pro release 2.1 supports capturing and parsing the new Chart Filters.

Want to up your Qlik Development game? Register for the Masters Summit for Qlik — Madrid 19 Sept or New Orleans 14 Nov — three days of advanced Qlik Developer training and tips.

-Rob

Share

If() Tips

Summary:  I offer some tips for writing better performing and easier to maintain syntax when using the Qlik If() function. 

The Qlik If() function is very powerful and  frequently appears in Qlik Sense and QlikView apps.

Expressions using multiple If() functions can easily get out of hand and become difficult to maintain or debug, as well as poor performers.

In this post I’ll offer some advice on avoiding If() pitfalls and tips to write easier to understand expressions.

The Qlik syntax diagram for the If function is:

if(condition , then [, else])

That’s perfectly clear to most people, but I prefer to think of it more like:

if(condition , true result [, false result])

Tip#1: If() does not short circuit.

Both the true & false branches are calculated even when only one is possibly true.  For example:

If(Only(Currency = 'LC',  Sum(Sales), Sum ([Sales LC])

In this case both Sum() expressions will be calculated even though only one value will be utilized.  In most cases this behavior is not of concern and in many applications will perform very well.   However, a nested If() with many possible branches or a large data set may perform poorly.

For more on the short circuit issue see “How to Choose an Expression“.

 

Tip#2: Use indentation sparingly.

The true or false result may be an additional, “nested” If(), which is where we start to see some ugly syntax.  Following traditional programming conventions many people automatically indent the nested if like this:

If(Sum(Sales) > 100000, 'Large',
    If(Sum(Sales) > 75000, 'Med', 
      If(Sum(Sales) > 50000, 'Demi',  'Small')
    )
)

Essentially,  the expression above classifies into one of four values.  I don’t think indentation  adds to the readability and indentation will lead you into “tab hell” when you get many possibilities.  I prefer to write this expression as:

If(Sum(Sales) > 100000, 'Large'
,If(Sum(Sales) > 75000, 'Med' 
,If(Sum(Sales) > 50000, 'Demi'
,'Small'
)))

No indentation, all the closing right parens collected on one line at the end. Makes it very easy in the expression editor to see that you have the right number of parens.

The leading (vs trailing) commas are my personal preference.  This make it easier to comment out logic and in my view, the comma belongs to the If that follows it, not the preceding If.

I think the above syntax makes it very easy to understand that I am choosing  one of four results, and what the rule is for each result.  Syntactically each If() is the else parameter of the preceding If().  I don’t think of the Ifs as “combined”, rather as “sequential”.

Do indent when you are using If() as the then parameter,  as shown in Tip#4 below.

 

Tip#3: Simplify by testing from high to low. 

The business rule that created this sample expression may have been stated to the Qlik developer like this:

“Classify sales of 0 to 50 000 as “Small”, 50 001 to 75 000 as “Demi”, 75 001 to 100 000 as “Med” and above 100 000 as “Large”.

The developer may faithfully translate the requirement into this expression.

If(Sum(Sales) > 0 and sum(Sales) <= 50000, 'Small'
,If(Sum(Sales) > 50000 and Sum(Sales) <= 75000, 'Demi', 
,If(Sum(Sales) > 75000 and <= 100000, 'Med'
,'Large'
)))

This returns the correct result. Testing from low to high values forces the use of “and” which makes the expression more complex than necessary and potentially slower to execute.  In my experience, testing from high to low, as in the Tip#2 example, yields a cleaner syntax.

 

Tip#4: Use “and” when you mean and.

Here’s a sample expression requirement:

When Sales > 1000 and Region=’US’, it’s “Mega US”. When Sales > 750 and Region = ‘UK’, it’s “Mega UK”. Otherwise it’s “General”.

I have seen this written as:

If(Sum(Sales) > 1000, 
    If(Region = 'US', 'Mega US'),
If(Sum(Sales) > 750, 
    If(Region = 'UK', 'Mega UK'), 
'General')

While the “and” requirement may be satisfied with a then-if  nesting, I find it clearer with the “and” keyword.

If(Sum(Sales) > 1000 and Region = 'US', 'Mega US'
,If(Sum(Sales) > 750 and Region = 'UK', 'Mega UK' 
,'General'
))

What if the requirement for  both US & UK were 1000?  You could argue that this is clear case for nesting in that there is a shared  condition and perhaps it would be a good practice to not repeat ourselves on the Sum(Sales).

If(Sum(Sales) > 1000, 
    If(Region = 'US', 'Mega US',
    If(Region = 'UK', 'Mega UK'), 'General'), 
'General')

Notice  we needed to repeat the ‘General’ result to cover the null case.  So it’s not super clean, but it may be worth it to not repeat the sum(Sales) calculation.  Generally I find the performance difference between “and” and “nested if” to be insignificant and tend to favor whatever is the clearer syntax for the given requirement.

What about Pick(Match())? 

I’ve heard it occasionally claimed that a Pick/Match combination will run faster than a nested If.   The expression might look like this:

Pick(
    Match(
      -1
      ,Region= 'US' and Sum(Sales) > 1000
      ,Region= 'UK' and Sum(Sales) > 1000
      , -1
    )
,'Mega US', 'Mega UK','General')

In my own testing and reading I’ve never found any performance advantage to Pick/Match.  That said, sometimes the syntax is appealing.

One thing I don’t like about Pick/Match is the distance between the condition list  and the result list. It’s fairly easy to get the lists  mis-aligned as the expression grows.

I  wish Qlik had a Switch type function like:

Switch (
  condition1 : result1
  [,condition2 : result2, ...]  
  [: defaultResult]
)

 

Tip#5: Simplify by using Column(n) or Measure Name

If your if() refers to something that has already been calculated in the chart, you can use the Column(n) function to refer to the value of a measure/expression column. For example, in a color expression:

If(Column(2) > 0, Green(), Red())

This can be much neater than repeating the expression text and typically runs faster as well.

If you are on Qlik Sense May 2021 you can use Master Measure names in the expression like:

If([Total Sales] > 0, Green(), Red())

[Total Sales] need not be a measure in this chart.

Both QlikView and Qlik Sense also allow you to reference the Label of a measure/expression column in the chart. In most versions the syntax checker will declare this an error even though it calculates correctly. I tend to avoid the label technique due to this confusion.

 

Tip#6: Don’t use If() as a chart filter

Use If when you want to dynamically select from two or more alternatives.  If should not be used simply to filter data like this:

Sum(If(Region = 'EU' and CYTDFlag = 1, Sales)

Filtering is best done with Set Analysis. The same expression written with a Set:

Sum({<Region={'EU'}, CYTDFlag={1}>} Sales)

Set Analysis is much faster than If.  If you are new to Set Analysis, you might initially find the syntax more challenging than If.  But SA  is much more powerful than If and well worth mastering.

 

Tip#7:  Consider the other conditional functions. 

Alt() and Coalesce() can be a more compact and elegant approach to testing for nulls. Instead of:

If(IsNull(SalesRep), Manager, SalesRep)

use:

Coalesce(SalesRep, Manager)
// If you want to consider empty and 
// blank strings as Null:
Coalesce(EmptyIsNull(Trim(SalesRep)), Manager)

When testing against a list of values,  instead of multiple If() or “or”, use the Match() or WildMatch() functions instead.

If (Match(StateCode, 'VA', 'TN', 'FL', 'GA'), 'South',  'Other')

 

I hope you find these tips useful.  You can use my QSDA Pro tool to quickly filter and examine all the uses of the If() function in a Qlik Sense App, located on-prem or in SaaS.

-Rob

 

Share

Deconstructing Visualization Performance

Have you ever had a Qlik Sense Visualization take longer to calculate than you like?  You may have been measuring the response time with QSDA (or the older QVF QS Document Analyzer) ,  Add Sense or a stopwatch.

Your chart is likely made up of several expressions — Measures, color expressions, maybe reference lines.  Which expression(s) are the hogs? Some? All?

In QSDA Pro version 1.4 I’ve introduced a “Viz Deconstruction” feature that calculates each chart expression individually and lets you see where time is spent.  In the QSDA Pro Viz page, click the  button under a Viz:

Clicking the  button will pop up the Viz Deconstruction dialog.  Each expression in the chart will be executed and reported individually.

For this chart I can see that one Measure takes much longer than any other expression.  Now I know where to focus my efforts if I want to improve response time.

Here’s another example. Big chart, lots of data, seven Dimensions.

The Measures require over seven seconds each to generate  7M+ output rows. Maybe less detail would result in better performance and a more usable chart.  I’ll uncheck a few Dimensions and 

Wow! Big improvement in calc time and a more manageable number of rows.

As a final example we’ll see that long calc times are not always due to Measures.

The Measures take only a few milliseconds to calculate.  But the color expression takes more then 1/2 second.  Can we do this coloring more efficiently?

You may already be deconstructing charts by making clones, commenting code and remeasuring.   Of course, you have to deal with the effects of caching and cleaning up after yourself.  QSDA Pro provides a much faster and more structured approach.

QSDA Pro is free to try with applications containing 100 or fewer objects.  If you want remove the limits, get support or acknowledge the value you get from QSDA, purchase a Monthly or Annual subscription.

I hope you find the Viz Deconstruction feature useful. I have!

-Rob

Note: There was a bug in the 1.4.0 version that kept the Deconstruction feature from calculating when using a DESKTOP Connection. This has been fixed in version 1.4.2.

 

Share

QSDA Pro as a Quality Tool

I my last two posts I introduced QSDA Pro and the flag workflow.  In this post I’m going to show how you can use QSDA Pro to quickly uncover quality issues in your Qlik Sense App.

QSDA groups flags by category, and the “Quality” category is where I usually begin my app review.  Quality flags are used to indicate where something appears to be seriously broken such as:

  • An expression syntax error.
  • A reference to a Master Measure that no longer exists in the library.
  • A missing extension.
  • A bookmarked field that is no longer exists in the data model.

These are the kinds of things you want to discover before your users do!

It’s relatively easy to create new errors in an existing Qlik app.  Qlik does not warn or block you from deleting a resource like a Field or Measure that is in use.

A properly done field rename may update names in expressions.  But it will not update variables or bookmarks.  And I’ve seen plenty of field renames go wrong and break expressions as well.

It can be difficult to detect all app errors by visually checking your app.  The broken piece may be a color expression or a calculation condition that isn’t readily visible.

So yes, easy to break, sometimes hard to detect and heros find  problems before their users do.  Enter QSDA Pro.

In QSDA menu,  Help -> Flag List will display a list of all potential flags in the installed version. Here’s the current list for Quality (The “{}” bits are values that will get filled in when the flag is created.)

Some of these are fairly straightforward, some a bit more subtle.

Why would you have a missing Master Dimension or Measure?  A fairly common scenario is not understanding that Master Items are referenced by internal Id, not by Name.  So you create something called “Sales” and use it in several charts.  Later you are doing some exploration and create something called “Sales2” which you like better. So you think if you delete “Sales” and rename “Sales2” to “Sales” everything will be using the new “Sales”.  Wrong.

Because the master item is now gone, I can’t tell you what used to be in this master item.  You will have to rely on an app backup. Or…if you have a previous QSDA Analysis for this app you can look up the master item definition there!

All Expressions and Dimensions are validated using the Qlik syntax checker. A typical error might be a bad fieldname (perhaps because the field was removed from the model).

Flag Details will give us a detailed error message, location of this expression  (Master Library) and importantly the Use Count. This use count is zero so we know deleting the measure is a viable option.

QSDA uses the Qlik syntax checker so the test is only as good as what I  get from Qlik.  There are some limitations, for example when column labels are used in an expression.  You’ll note that the Qlik expression editor flags this as invalid so QSDA will pass this message on.

The “Parse Error” flag is raised when Qlik Syntax says the expression is ok but QSDA finds the expression does not conform to expected syntax rules. This could be a problem with the QSDA parser  (a work in progress) or it could be Qlik tolerating something unusual.  For example, do you think this is a valid expression?

sum({<Sales={">10 between < 90"}>}Sales)

The Qlik syntax checker will declare it valid. And it will return an official looking result — which may or may not be correct.  But what is that “between” keyword? (if you’re curious why “>10 के बीच < 90” also seems to work, see https://masterssummit.com/something-to-get-confused-about/).

Nervous about refactoring or changing your apps?  Can you relate to “Change Paralysis”  or Fear Driven Development (FDD)?  We all need a tool like QSDA Pro to proactively plan changes as well as audit after the fact for unintended errors.

Download QSDA Pro now and analyze some of your own apps.  QSDA Pro is free to use during the beta period.

You can analyze published and unpublished apps, so go ahead and take a look at some of those production apps.   Let me know in the comments if you find something interesting!

-Rob

 

 

 

Share