topic Re: Set Modifier Syntax in QlikView

Set Modifier Syntax

Wed, 08 Feb 2012 16:01:39 GMT

Hi All,

I want to add an expression to a straight table, where the definition needs to refer to more than just one field in the expression (apologies if I don't have the terminolgy correct).

The initial expression works fine:-

=sum({$<[BRANCH-CODE]={'DOBCROSS'}>}[WEEKLY-COSTOFGOODS])

I now wish to expand this so that it refers to an additional set of values which are contained within another column of the same straight table. :-

=sum({$<[BRANCH-CODE]={'DOBCROSS'},[ITEM-NUMBER]=column(2)>}[WEEKLY-QTY-SOLD])

The straight table shows the top 20 values based on the values in the first column which are the weekly cost of goods sold. I now wish to see the weekly qty of goods sold in the same table, but since these fields are different, I don't see the correct quantities sold,and the totals are for all items sold, not just those that are in the top 20. Hence, why I am trying to reference the item numbers (primary key) for those in the top 20 to only show the sold quantites for those items.

Hopefully someone can point me in the right direction.

Thanks

Set Modifier Syntax

stephencredmond — Wed, 08 Feb 2012 16:11:45 GMT

Hi,

Need a little more information. If possible a sample doc would be really helpful.

Top 20 what?

Regards,

Stephen

Set Modifier Syntax

Wed, 08 Feb 2012 17:18:41 GMT

If nothing else, can anyone point me in the direction of a good source for Set Modifiers and expressions. I've used the reference guide but its not helping me that much.

All I really need to know is how to use multipe modifiers in the same expression - all I get told is there is a syntax error and I have no idea why, as it looks correct to me based on the reference material I have.

Thanks

Re: Set Modifier Syntax

Thu, 09 Feb 2012 08:59:45 GMT

I'm not sure how to supply an example document due to the size of data I'm working with, sorry. However I will try to explain a bit more about what I am attempting to achieve.

Straight table needs to show top 20 items in each deparment by their Gross Margin.

The dimensions in the table are Department and then Item.

The first expression in the straight table is the calculation for the Gross Margin.

The subsequent four expressions are the Gross Margin buy using Set Modifiers for each of the companies branches.

This works perfectly as the straight table is restricted to show the top 20 values based upon the first expression, which is the overal Gross Margin.

The issue is that the client wished to show several other fields of information, for example, Quantity Sold and Value of Good Sold, within the same table, and these values should only be for the items which appear in the top 20.

If I just add new expressions for these, the values shown aren't related to the items within the top 20, My solution to this, was to add another field into the straight table that shows the unique item number. Then, using set analysis, I want to link to the item number column in the straight table to only show the values of Quantity Sold, and Value of Goods sold, for those items in the top 20 based upon Gross Margin.

I hope this gives a bit more information and thus someone can point me in the right direction.

Thanks

Re: Set Modifier Syntax

perumal_41 — Thu, 09 Feb 2012 09:34:46 GMT

Try This Expression

=sum({$<[BRANCH-CODE]={'DOBCROSS'},[ITEM-NUMBER]=[column(2)]>}[WEEKLY-QTY-SOLD])

Regards

Perumal A

Set Modifier Syntax

Jason_Michaelides — Thu, 09 Feb 2012 09:37:31 GMT

Are you able to make some selections to limit the data set, then Reduce Data and post an example?

Re: Set Modifier Syntax

stephencredmond — Thu, 09 Feb 2012 09:39:14 GMT

Hi Trevor,

How are you restricting the Product to top 20?

If I was doing it (prior to v11), I would be using an AGGR in a calculated dimension something like this:

=If(Aggr(Rank(Sum(Margin)), ProductName) <= 20, ProductName, Null())

Then set that dimension to suppress null values.

This way, I only get the items, for each Department, that are in the top 20 for that department. Any other expressions that I add will be only for those items.

Regards,

Stephen

Re: Set Modifier Syntax

stephencredmond — Thu, 09 Feb 2012 09:51:04 GMT

Hi Perumal,

This will not work.

column(2) refers to a calculation - the result of an expression. Not an item number. Adding square barckets doesn't make it any better as this is still syntactically incorrect.

The best way to remember what goes in a set is that it is a set of selections. The values in the set (surrounded by { } ) will be, basically, anything that you can type into a search dialog of a List Box.

Here are examples of valid sets:

Sum( {<[ITEM-NUMBER]={'12345'}>} Sales)

Sum( {<[BRANCH-CODE]={'XYZ'}, [ITEM-NUMBER]={'12345'}>} Sales)

Sum( {<[BRANCH-CODE]={'XYZ', 'A?C'}, [ITEM-NUMBER]={'12345', '235*'}>} Sales)

Sum( {<[BRANCH-CODE]={'XYZ', 'A?C'}, [ITEM-NUMBER]={'=Sum(Sales)>50000'}>} Sales)

Sum( {<[BRANCH-CODE]={'XYZ', 'A?C'}, [ORDER-DATE]={">=01/01/2010<=31/12/2011"}>} Sales)

As shown in my previous response, you do not always need to go to sets to come up with a solution.

Regards,

Stephen

Re: Set Modifier Syntax

Thu, 09 Feb 2012 10:17:16 GMT

Hi Stephen,

Yes, working in V11 so am using the inbuilt restriction to top 20 on the Dimension Limits tab. I assume that this functionality is put there for quick and easy solutions to more simplistic data views, and that perhaps in this case I'm doing something a little bit more involved that requires aggregation functions - this is new to me, so will look at your suggestion and also read the reference guide for more info.

Thanks for your quick response.

Trevor

Re: Set Modifier Syntax

stephencredmond — Thu, 09 Feb 2012 10:26:55 GMT

Hi Trevor,

If you are using the v11 functionality to restrict the 2nd dimension of the chart (Item) to the top 20 values of the first expression, then any other expressions show will only be relevant to those 20 items.

Are you saying that you are then seeing more than 20 items?

Regards,

Stephen

Re: Set Modifier Syntax

Thu, 09 Feb 2012 10:40:32 GMT

Hi Stephen,

The values themselves look ok, it is that the totals for each department, I would have assumed, would show the sum of the quantity sold for the 20 items shown above, but it seems to show the total for all items sold in the department.

Is this expected behaviour or is there something I'm not doing or doing wrong, or that I'm wrong in my assumption?

Thanks

Trevor

Re: Set Modifier Syntax

stephencredmond — Thu, 09 Feb 2012 11:35:00 GMT

Hi Trevor,

The default total in a straight table is the "Expression Total". You probably want to change the total mode to Sum of Rows.

Regards,

Stephen

Re: Set Modifier Syntax

Thu, 09 Feb 2012 13:53:48 GMT

Hi Stephen,

Thanks for this, but when i change this on the Quantity column it appears to have no affect at all. Do I have to do this for each expression in the table, or should it work just on the selected column?

Thanks

Trevor

Re: Set Modifier Syntax

stephencredmond — Thu, 09 Feb 2012 14:02:53 GMT

Hi Trevor,

You will need to do this on every column that you need to do it on.

The "Expression Default" would just calculate the expression, e.g. Sum(Value), as if there were no dimensions.

Actually, my method with the Aggr would work better in that instance because every other item would have "Null" as its value in the chart and, with Nulls suppressed, the value would calculate correctly.

Regards,

Stephen

Re: Set Modifier Syntax

Thu, 09 Feb 2012 14:41:24 GMT

Hi Stephen,

I've just added a word document that shows a screen shot of the straight table in question - not sure if this will be more helpful, but hopefully it displays the issue with the totals that I'm having.

Thanks

Trevor

Set Modifier Syntax

stephencredmond — Thu, 09 Feb 2012 14:58:34 GMT

Something to do with the totals from the Dimension limitations.

Try the AGGR approach.

Regards,

Stephen