Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Partner
Partner

ignore all selections except for some selected columns

hello

we all know that if we want to ignor the selection of a column whose name is Column_a for example then in the set analysis of the expression we put Column_a=

Now I got an expression in which I want to ignore all selections of all columns except for the columns year, and month

suppose I got the following expression

sum(Sales)

how will the syntax be knowing that I got in the schema a huge number of columns

Please advise

I can walk on water when it freezes
8 Replies
Not applicable

Re: ignore all selections except for some selected columns

Hi Ali,

Just try something like that :

Sum({1<Year={2012}, Month={'October'}>} Sales)

In SetAnalysis, precising 1 means "ALL DATA" without taking care of the selections done (the opposite is $)

MVP
MVP

Re: ignore all selections except for some selected columns

The problem with that is that it hardcodes the values.  You might think it's a simple problem to instead say "give me the selections in those fields", but it's non-trivial.  Using P() might seem an obvious solution:

sum({1<Year=P(),Month=P()>} Sales) // not quite right

But what is possible in Year and Month is still sensitive to what else has been selected, so isn't truly ignoring the selections.  Now, you can make up some very complicated syntax to check if selections are made in the field, and if so build a list of those selections.  But that turns out to be a rather long expression.  I'd post it, but I'm not finding an example, and don't want to take the time to figure it out again when it isn't what I'm going to suggest anyway.  Instead, use the concat() function and the $Field variable to list every field to ignore except the ones we care about.  Like this:

sum({<$(=concat({1<$Field-={'Year','Month'}>} '[' & $Field & ']=',','))>} Sales)

It's ugly and complicated, but it's the best way I know, the cleanest way I've seen to get the right results in all cases instead of just something close.

Edit: added "1" to the set analysis building the list of fields to exclude just in case someone's application allows users to make selections in $Field.  We likely wouldn't want those selections affecting our list.

Employee
Employee

Re: ignore all selections except for some selected columns

If you are running QV11 make use of alternate states. Create a new state under Document Properties. Set your chart to that state under the general tab and write an expression like,

sum({<Year= $::Year, Month = $::Month>} Sales)

It does the same thing as John suggest but allows you add listboxes for that state later on if you like without having to change your expressions. This would also allow you do to Sum(Sales) and have listboxes for Year and Month set to the state you created and you created a "disconnected" chart.

Not applicable

Re: ignore all selections except for some selected columns

I am attempting to implement your concat() function solution to find the maximum weekend in the current year (max year with data), with users selecting 1 of 2 week type and 1 of 2 calendar type options to define the calendar.  I want the maximum week in the whole year, not the maximum with data.  It seems that the set analysis is basically ignoring all of fields and it is returning the maximum weekend in the entire calendar.

Here is my expression: 

=max({<$(=concat({<$Field-={'Year={$(=min(Year))}','WeekType','CalendarType'}>} '[' & $Field & ']=',','))>} WeekEnd)

I also tried the the =P() as a possible solution with no luck.

Do you have any ideas as to what I could be missing? 

Highlighted
MVP
MVP

Re: ignore all selections except for some selected columns

If you understand how the expression works, it's a simple and easily-fixed oops.  You can skip to the end if you want the solution, but I think it would be helpful to review exactly what we're doing here rather than just cutting and pasting and calling it good.

Now, I'm not sure how much you understand or don't understand about what's going on in the expression, and even if you understand it, others who find this later may not, so let's start over from scratch.  Let's go through the entire thought process that leads us to the expression I posted, though I guess I'll assume a decent knowledge of set analysis itself.  Well, what we want is the behavior we'd get if we had something like this with nearly every field in it:

sum({<Customer=,Order=,Product=,...>} Sales)

All of those equals signs with no set after them say to ignore selections in that field, which is what we're after.  But in a real application, with 100 fields, say, it's completely impractical to write them all out, and then maintain them as we add and remove fields from our data model.  So what we'd like is some way for the system itself to list out all of our fields.

The system has a field called "$Field" where it tracks the name of every field in the data model.  So let's say we did something like this:

concat($Field,',')

That would give us this string:

'Customer,Order,Product,...'

It's a string, though, so not directly useful.  However, it's possible to evaluate and then insert a string into an expression by using dollar sign expansion, which is to say $(=...).  Something like this:

sum($(='Sales'))

Gets turned into this:

sum(Sales)

Before being executed.  The dollar sign expansion always comes first, with the result inserted literally into the expression.  So then if we did this:

sum($(=concat($Field,',')) Sales)

We'd actually be processing this:

sum(Customer,Order,Product,... Sales)

That's not a legal expression, but at this point, we can just add the {<...>} and the equals signs to get a legal expression:

sum({<$(=concat($Field & '=',','))>} Sales)

Which the system would interpret like this:

sum({<Customer=,Order=,Product=,...>} Sales)

Perfect!  So why aren't we done?  Well, one reason is that hidden in the ... are Year= and Month=, so we're excluding every field.  What we really want is every field EXCEPT for Year and Month.  Well, every field but year and month is just a set analysis expression:

{1<$Field-={'Year','Month'}>}

So we need to insert that into our concatenation so that it skips those fields:

sum({<$(=concat({1<$Field-={'Year','Month'}>} $Field & '=',','))>} Sales)

The other reason we aren't done is that field names can contain whitespace.  A field like [This is a Field] must be enclosed in brackets (or double quotes).  However, the $Field field doesn't contain the brackets.  So we have to string '[' and ']' around the field in our current expression.  And that's how we end up with our final expression:

sum({<$(=concat({1<$Field-={'Year','Month'}>} '[' & $Field & ']=',','))>} Sales)

So now, with an understanding of exactly what that expression is doing, let's take a look at your expression:

max({<$(=concat({<$Field-={'Year={$(=min(Year))}','WeekType','CalendarType'}>} '[' & $Field & ']=',','))>} WeekEnd)

Now, the section of this where we do the $Field-={...} is where we're telling the system ONLY the names of fields that we do not want it to ignore.  Year={$(=min(Year))} will in this case probably end up looking like Year={2012}, but the expression is going to interpret that as a field NAME, not as some selection you're making in the Year field.  Since there's no such field name as "Year={2012}", I THINK the instruction will end up getting ignored, and the Year field will therefore be in the list of fields we ignore.  You'd therefore get the maximum WeekEnd in the entire data set, not the maximum weekend in the minimum year, which is I believe the problem you're seeing.

So instead, we first just want to exclude the Year field from the concat(), which is to say to INCLUDE it in the list of fields we care about selections in.

max({<$(=concat({<$Field-={'Year','WeekType','CalendarType'}>} '[' & $Field & ']=',','))>} WeekEnd)

Then the part where you're selecting a specific year has to go into the outside bit of set analysis, not the inside bit, because the outside bit is where we're actually picking what WeekEnds we care about (instead of field names).  So I think you want this:

max({<Year={$(=min(Year))}, $(=concat({<$Field-={'Year', 'WeekType', 'CalendarType'}>} '[' & $Field & ']=',','))>} WeekEnd)

Not applicable

Re: ignore all selections except for some selected columns

Wow, thank you so much for such a detailed explanation.  I only partially understood what the expression was doing, I got the gist of listing each field using the variable, excluding explicit exceptions, and ignoring all selections for those fields.  I did not understand it enough to draw the conclusion that the Year set needed to be outside of the concat.  Understanding this will be very useful as I have many situations where I have listed out all of the fields available for users to filter, understanding that it was not all-inclusive of filter possibilities.

Thank you again!

Not applicable

Re: ignore all selections except for some selected columns

Thanks Alex

<Field = $::Field> saved my Day

sateeshkumar
Contributor

Re: ignore all selections except for some selected columns

Thanks alot, very good explanation.