topic Re: Set analysis comparing two fields in QlikView

Set analysis comparing two fields

Fri, 21 Nov 2014 14:52:25 GMT

Howdy, can someone help me get the third column in the left table working correctly...

I want to have set analysis to ...but its not working

Sum({<Master_WeekNum = {Quota_WeekNum}>}[Allocation %])

I am trying to bring over the allocation % from the table on the right...to match the same "Master_WeekNum" for the table on the left.

Much thanks!

-David

Re: Set analysis comparing two fields

Anonymous — Fri, 21 Nov 2014 15:07:24 GMT

Probably best to join the 2 tables in the load script, the you just need to put the dimension and 2 expressions into your chart it will associate and work.

Hard to give precise instructions.

Share a picture of your data model and maybe even the text of your load script and I am sure someone will assist.

Re: Set analysis comparing two fields

Fri, 21 Nov 2014 15:13:37 GMT

I was trying to keep them seperate in the model...is it possible to write the set analysis to simply compare the week number dimensions?

Re: Set analysis comparing two fields

jagan — Fri, 21 Nov 2014 15:18:45 GMT

Hi,

Try like this

Sum({<Master_WeekNum = {$(=Concat(DISTINCT Quota_WeekNum, ','))}>}[Allocation %])

Sum(If(Master_WeekNum = Quota_WeekNum, [Allocation %]))

Or in script simply join both the tables.

Hope this helps you.

Regars,

Jagan.

Re: Set analysis comparing two fields

petter — Sun, 26 Jul 2015 10:47:09 GMT

I know it is a long time ago this question was posed - but for the benefit of those that might search this up:

1. Your Set Expression is not entirely correct. To be able to compare two fields the second field must not be enclosed in curly braces
(curly-brackets) since the field itself constitutes a set.

So the expression should look like this:

Sum( { <Master_WeekNum=Quota_WeekNum> } [Allocation %] )

2. To be able to do a comparision you have to have one or more values selected in the second field (Quota_WeekNum) or the comparision will not be done. Possible values will not be included in the comparison - only selected values. That is according to the help in QlikView that states (the underscores and italics are mine):

Based on another field

A simple case is a selection based on the selected values of another field, for example <OrderDate = DeliveryDate>. This modifier will take the selected values from DeliveryDate and apply those as a selection on OrderDate. If there are many distinct values – more than a couple of hundred – then this operation is CPU intense and should be avoided.