topic Building charts with static selection- Detatch or Set Analysis in QlikView

Building charts with static selection- Detatch or Set Analysis

jason_nicholas — Wed, 25 Nov 2020 16:16:04 GMT

I have done some digging into my issue, and I have found many solutions which describe Set Analysis (and which I have had a hard time following) and a few others which discuss the Detatch function. I would appreciate a review of my use case and some insight on how best to proceed.

I'm building a function to analyze support bookings, and I am trying to make the end result as user-friendly and click-less as possible. I've worked my way through much of the LOAD work, and now I need to develop the required displays. My question comes in two parts. I'll put part 2 in the comments to keep organized.

Part 1

I have a bar chart using the dimensions MONTH and REGION and an expression of COUNT(DISTINCT([Booking ID]))

this results in two bars per month (North America and Europe), each counting the number of Booking IDs which fit a set of selections. I would like this result to display in a chart without having to make the selections, and the result should ignore of any selections made.

to make it work now, I select the option "Dedicated" from the field [Charge Type], "Active" from the field [Status], and two other selections in separate fields (lets say "AAA" from [Field1] and "BBB" from [Field2]). This limits my data to exactly the total I want.

It seems Set Analysis might be the solution, but I don't fully understand the syntax in the examples I have read. If this is the way to go, please help me understand the expression required.

Otherwise, I could set the chart as I need it and then choose "Detatch". This may well be the solution, but I am unsure if this data will update as I reload my data set each month. The selections will always remain the same, but the number of DISTINCT([Booking ID]) will increase as new data is added. Can I rely on Detatch to handle this?

Re: Building charts with static selection- Detatch or Set Analysis

jason_nicholas — Tue, 20 Dec 2016 16:42:38 GMT

Part 2

I am looking to capture other data in the same chart, which requires a different set of filters. For example, to capture the Asia data, I cannot use the "Dedicated" selection from [Charge Type], because this field is not used in the region. Instead, I deselect 'Dedicated" and replace it with "Asia" from a field [Region].

Without the "Dedicated" selection, my Asia data remains correct, but without the "Asia" selection, I continue to receive North America and Europe data, but those numbers are now incorrect because of the missing "Dedicated" filter.

What function could I use to calculate two regions based on one set of filters and the third region based on another set of filters? Can I have both data sets showing at the same time?

It follows that once I get these three data sets showing properly, I would want to use the results from my question above to make the chart agnostic of any selections.

Re: Building charts with static selection- Detatch or Set Analysis

sunny_talwar — Tue, 20 Dec 2016 17:23:19 GMT

May be this for Part 1

Count(DISTINCT {1<Field1 = {'AAA'}, Field2 = {'BBB'}, Date = {"=Date >= MonthStart(Max({1}TOTAL Date), -1) and Date<= MonthEnd(Max({1} TOTAL Date)"}>} [Booking ID])

Re: Building charts with static selection- Detatch or Set Analysis

sunny_talwar — Tue, 20 Dec 2016 17:24:19 GMT

It might help to share a sample with expected output

Re: Building charts with static selection- Detatch or Set Analysis

jason_nicholas — Tue, 20 Dec 2016 18:23:18 GMT

Unfortunately, I am behind a firewall which prevents uploading any content to an external internet address, so I am not able to post examples.

I have attempted the formula provided above, but I think there is a syntax error because the system stops recognizing formula functions when the date section starts.

Here is my version:

count(distinct{1<[Charge Type]={'Dedicated'},Status={'Active'},[Test Filter]={'Remove Testing'},[Room Type]={'Event Space'},Date={"=Date>=MonthStart(Max({1}TOTAL Date),-1) and Date<=MonthEnd(Max({1} TOTAL Date)"}>}[Booking ID])

Aside from syntax, I'm a little unclear about the function. Are (), {}, and <> all used for the same purpose- to encapsulate and run a function in a specific order? Or do they have a specific purpose for each? I can't find a good reference for this.

What function does the 1 at the start perform?

What is the result of the Date part of the function? I can unpack the details of the function piece by piece to understand it better, and don't want to hide what might be a useful future question in this comment, but I'd appreciate a brief overview

Re: Building charts with static selection- Detatch or Set Analysis

sunny_talwar — Tue, 20 Dec 2016 19:14:21 GMT

Is your date field called Date or something else? I used Date as a placeholder for your actual date field name.

Each of these ( (){}<> ) play an important role in a set analysis syntax. You can read about this here:

A Primer on Set Analysis

For some more advance set analysis, you can look here:

Set Analysis: syntaxes, examples

The PDF attached in the above link isn't perfect, but it will be good starting point.

1 is used for ignoring all selections. This is covered in the second link I have shared above.

Date part is just saying that pick all dates where it is greater than one month before the max date and less than end of max month.

HTH

Best,

Sunny

Re: Building charts with static selection- Detatch or Set Analysis

jason_nicholas — Tue, 20 Dec 2016 19:53:07 GMT

Thank you. These links will be invaluable.

I split my date into Day, Month and Year from my LOAD statement. I tried replacing "Date" with "Month". I also adding the full date to the load in addition to the breakout, and replaced "Date" with "FullDate". the result of both efforts shows "No data to display"

As I will be away for a few days, I didn't want to leave the thread as "unanswered". I will keep tackling this when I return after studying your links above, and see where I come out.

Can you tell me the difference between using Set Analysis for this function vs Detatching the chart when my filters are selected?

Also, what direction could I head to solve Part 2?

Re: Building charts with static selection- Detatch or Set Analysis

sunny_talwar — Tue, 20 Dec 2016 20:02:46 GMT

Let me get back home and read them with a clear mind to answer part 2 and any left over questions you have above from part 1.

Re: Building charts with static selection- Detatch or Set Analysis

sunny_talwar — Wed, 21 Dec 2016 01:18:47 GMT

Can you share the exact expression you used? May be I might be able to pinpoint what is missing.

With regards to your part 2, I am going to give you a very general direction because I don't have enough information to get you the details. When you want two expression with different set of filters, you can Sum them using RangeSum() function

RangeSum(ExpressionWithFirstSetofFilter, ExpressionWithSecondSetofFilter)

Once you do this rows where only 1st expression has value will display those values. Rows where only 2nd expression has value will display those values. Rows where both expressions will have value will be summed together.

HTH

Best,

Sunny

Re: Building charts with static selection- Detatch or Set Analysis

jason_nicholas — Thu, 22 Dec 2016 19:59:33 GMT

In my case Date is called [Booking Date]. In fact, I load all of my data as:

YEAR([Booking Date]) as Year,

MONTH([Booking Date]) as Month,

Day([Booking Date]) as Day

But to match the function here, I added a simple [Booking Date] to the load script in front of these.

Here is the exact function I developed based on your suggestion.

count(distinct{1<[Charge Type]={'Dedicated Event'},Status={'Active'},[Test Filter]={'Remove Testing'},[Room Type]={'Event Space'},[Booking Date]={"=[Booking Date]>=MonthStart(Max({1}TOTAL[Booking Date]),-1)and[Booking Date]<=MonthEnd(Max({1}TOTAL [Booking Date])"}>}[Booking ID])

I think part of the problem is the double quotes in the date part of the function. When I include them, the function stops working after the first [Booking Date]. I know this, because that entry will auto fill but beyond that everything is treated like meaningless text. I tried without the quotes and I got all the way through the last [Booking Date] with QV recognizing the function. However, event then, the end of the function is unrecognizable.

Re: Building charts with static selection- Detatch or Set Analysis

sunny_talwar — Thu, 22 Dec 2016 20:13:12 GMT

Give this a shot

Count(DISTINCT {1<[Charge Type] = {'Dedicated Event'}, Status = {'Active'}, [Test Filter] = {'Remove Testing'}, [Room Type] = {'Event Space'}, [Booking Date] = {"=Only({1} [Booking Date]) >= MonthStart(Max({1} TOTAL [Booking Date]), -1) and Only({1} [Booking Date]) <= MonthEnd(Max({1}TOTAL [Booking Date])"}>} [Booking ID])

Re: Building charts with static selection- Detatch or Set Analysis

jason_nicholas — Fri, 23 Dec 2016 13:12:03 GMT

This gets closer.

On the one hand, QV recognizes the whole function in so much that it allows me to auto-fill the last [Booking ID].

However, some of the functions I expected to turn blue in the command, like AND, do not. After the first

[Booking Date]={

everything is red text, which indicates there might be something wrong.

Also, the totals are coming out to 0. I have confirmed the spelling of all of the filters, and have an identical chart which provides the correct total when I manually select [Charge Type], [Status], [Test Filter] and [Room Type] to the choices selected here.

How does the 'Detatch' function I mentioned earlier play in to this? If I were to select the filters I want and detatch the chart, would the totals update as new data is imported? Or does Detatch lock it down to the results of the time?

Re: Building charts with static selection- Detatch or Set Analysis

sunny_talwar — Fri, 23 Dec 2016 13:20:12 GMT

And doesn't turn blue because it is within the double quotes part of the set analysis. There is nothing wrong in there. The thing within the double quotes section is again how I would expect it to be. A brief intro to set analysis might help

A Primer on Set Analysis

Just the total is 0 or each and every single line is 0?

With regards to detach, I am not full sure how it works, but I guess we can create a small sample to test it out. I will get back to you in a bit to confirm about detach

Re: Building charts with static selection- Detatch or Set Analysis

sunny_talwar — Fri, 23 Dec 2016 13:21:55 GMT

Just checked and the reload does update the chart with the new values even if the chart is detached.

Best,

Sunny

Re: Building charts with static selection- Detatch or Set Analysis

jason_nicholas — Fri, 23 Dec 2016 14:26:13 GMT

This chart lists the number of distinct Booking IDs across a range of 7 business groups. Each of those totals 0, and the grand total equals 0. Using the unmodified copy of the chart and manually making the selections, the total comes out to about 7,000. Which means something in the function is generating a "false" response. That's why I thought I had a spelling error, but that doesn't seem to be the case.

If DETATCH will solve my problem, it might be the simplest way to go. In fact, it would make my whole overall process easier. But I can't test it myself until after the first of the month when there is new data to import. So, for the time being, I would be happy to continue trying to nail down this function. I'm learning a lot in the process, and greatly appreciate your efforts.

I tried removing the entire date section, leaving the following:

Count(DISTINCT {1<[Charge Type]={'Dedicated Event'},Status={'Active'},[Test Filter]={'Remove Testing'},[Room Type]={'Event Space'}>}[Booking ID])

This worked, and I get the total I am looking for over the entire year, regardless of the active selections.

What is the purpose of the following section?

[Booking Date] = {"=Only({1} [Booking Date]) >= MonthStart(Max({1} TOTAL [Booking Date]), -1) and Only({1} [Booking Date]) <= MonthEnd(Max({1}TOTAL [Booking Date])"}

If possible, can I change it so that active date selection still works, but the four filters remain permanent? In other words, I want the [Charge Type], [Status], [Room Type] and [Remove Testing] options to be permanently fixed to the settings from the first formula above, but have the rest of the total calculate based on the dates I have selected?

I know the operator $ is used to imply current selections, but I don't know how to apply it so it will only reference current date selections and no others.

Re: Building charts with static selection- Detatch or Set Analysis

sunny_talwar — Fri, 23 Dec 2016 14:46:19 GMT

Going over this first

[Booking Date] = {"=Only({1} [Booking Date]) >= MonthStart(Max({1} TOTAL [Booking Date]), -1) and Only({1} [Booking Date]) <= MonthEnd(Max({1}TOTAL [Booking Date])"}

The best way to see what this is doing is to create a chart with

Dimension -> Booking Date

Expression -> =Only({1} [Booking Date]) >= MonthStart(Max({1} TOTAL [Booking Date]), -1) and Only({1} [Booking Date]) <= MonthEnd(Max({1}TOTAL [Booking Date])

My hope is that when you create this chart, the chart will show -1 for all dates starting the monthstart for one month before the max month to monthend for max month. For example, if you max date is 12/23/2016, you would see 11/01/2016 to 12/31/2016 to be all -1 (and might see 0 for all other dates if you have unchecked 'Suppress Zero Values' on the presentation tab of chart properties.

If you are not seeing what I just mentioned, then I must have made a typo, or something else might be missing from the syntax above.

With regards to your second question

Yes you can do this in bunch of ways based on your requirement

1) Ignore all selections except Date field selections

Count(DISTINCT {1<[Charge Type] = {'Dedicated Event'}, Status = {'Active'}, [Test Filter] = {'Remove Testing'}, [Room Type] = {'Event Space'}, [Booking Date] = p([Booking Date])>} [Booking ID])

Count(DISTINCT {1<[Charge Type] = {'Dedicated Event'}, Status = {'Active'}, [Test Filter] = {'Remove Testing'}, [Room Type] = {'Event Space'}, [Booking Date] = $::[Booking Date]>} [Booking ID])

1 is used to ignore all selections. There is a small difference between the above two expressions, but based on your requirement you might need one or the other.

Booking Date = p(Booking Date) allows for possible values for Booking Dates. So for example if you select a country where there are only 2 of the 3 booking dates available, your chart will show only 2 of the dates. Although you have not made any direct selections in Booking Dates, but the chart is still filtered. On the other hand Booking Date = $:: Booking Date will only filter based on direct selection of Booking Date field. In our previous example, you would still see 3 Booking Dates because you have not made selection in the Booking Date field.

2) Allow all selections except the few you have mentioned in your set analysis

Count(DISTINCT {$<[Charge Type] = {'Dedicated Event'}, Status = {'Active'}, [Test Filter] = {'Remove Testing'}, [Room Type] = {'Event Space'}>} [Booking ID])

Now you don't need Booking Date = p(Booking Date) or Booking Date = $:: Booking Date because the $ will take care of your selection in Booking Date field. But this expression will be fixed for Charge Type, Status, Test Filter, & Room Type, but will change based on all other selections. You can remove $ if you wish, because not having one is defaults to $. So the below will work the same way

Count(DISTINCT {<[Charge Type] = {'Dedicated Event'}, Status = {'Active'}, [Test Filter] = {'Remove Testing'}, [Room Type] = {'Event Space'}>} [Booking ID])

I hope this will clarify some of your doubts.

Best,

Sunny

Re: Building charts with static selection- Detatch or Set Analysis

jason_nicholas — Fri, 23 Dec 2016 15:28:53 GMT

We have a Winner!

Count(DISTINCT {$<[Charge Type] = {'Dedicated Event'}, Status = {'Active'}, [Test Filter] = {'Remove Testing'}, [Room Type] = {'Event Space'}>} [Booking ID])

In your first example for the demo chart, I received an error. it said " ) expected in expression". I added one at the end, but got the same result. both [Booking Date]=p([Booking Date]) and [Booking Date]=$::([Booking Date]) resolved to errors. But the last option is exactly the solution I need.

now, for Part 2, is this the correct Syntax?

RangeSum(Count(DISTINCT {$<[Charge Type] = {'Dedicated Event'}, Status = {'Active'}, [Test Filter] = {'Remove Testing'}, [Room Type] = {'Event Space'}>} [Booking ID]),COUNT(DISTINCT{otherSimilarExpressionWithDifferentFilters})

Or do I have to put RangeSum inside the Count(Distinct expression?

Re: Building charts with static selection- Detatch or Set Analysis

sunny_talwar — Fri, 23 Dec 2016 15:32:03 GMT

You will need this to be outside of the Count expressions:

RangeSum(CountExpression1, CountExpression2)

Re: Building charts with static selection- Detatch or Set Analysis

sunny_talwar — Fri, 23 Dec 2016 15:34:34 GMT

Jason Nicholas wrote:

In your first example for the demo chart, I received an error. it said " ) expected in expression". I added one at the end, but got the same result.

Got the same result? Meaning the result we expected to see?

Re: Building charts with static selection- Detatch or Set Analysis

jason_nicholas — Fri, 23 Dec 2016 15:35:33 GMT

same result, meaning an error expecting another ) in the expression