Building charts with static selection- Detatch or Set Analysis

[jason_nicholas]

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?

[sunny_talwar]

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])

[jason_nicholas]

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?

[sunny_talwar]

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

[sunny_talwar]

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

Best,

Sunny

[jason_nicholas]

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.

[sunny_talwar]

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])

or

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

[jason_nicholas]

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?

[sunny_talwar]

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

RangeSum(CountExpression1, CountExpression2)

[sunny_talwar]

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?

[jason_nicholas]

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