Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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

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
Contributor II

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

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

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

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

Best,

Sunny

Highlighted
jason_nicholas
Contributor II

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

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

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
Contributor II

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

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

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

RangeSum(CountExpression1, CountExpression2)

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

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
Contributor II

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

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