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?

[jason_nicholas]

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.

[sunny_talwar]

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

[sunny_talwar]

It might help to share a sample with expected output

[jason_nicholas]

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

[sunny_talwar]

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

[jason_nicholas]

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?

[sunny_talwar]

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.

[sunny_talwar]

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

[jason_nicholas]

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.