topic Set Analysis with 3 Fields in QlikView

Set Analysis with 3 Fields

Thu, 18 Nov 2010 07:24:03 GMT

I have three fields: Units, Period, Customer

I am trying to use Set Analysis to create a set of sum(Units) where Period = 3 and the Current customer is selected (this value is at least one selected).

I need to use the whole available set not just current because the user can change the Period selection. What is the best way to do this? I have tried

sum({1<Customer=, Period ={'3'}>} Units) and many other possibilities but I still can't get it to function properly. Thanks for your help.

Set Analysis with 3 Fields

Miguel_Angel_Baeyens — Thu, 18 Nov 2010 08:33:30 GMT

Hello Michael,

I'm afraid I'm missing something. Your code above seems fine, since you are using "1" (all possible values), "Customer=" (Ignoring user's selection in field Customer) and Period = {3} (selecting value "3" in field Period). Which behavior is expected? If the user is allowed to change the period, the expression above should work anyway.

Regards.

Set Analysis with 3 Fields

Thu, 18 Nov 2010 15:16:37 GMT

It appears that it is returning the sum for ALL Customers, where I need only the sum for the current selected customer. How do I make it do that?

Set Analysis with 3 Fields

Miguel_Angel_Baeyens — Thu, 18 Nov 2010 16:17:27 GMT

Hello MIchael,

If you want the user's selection in Customer

sum({1< Period = {'3'} >} Units)

In your example above you were ignoring users's selection for field Customer.

Hope that helps

Set Analysis with 3 Fields

johnw — Fri, 19 Nov 2010 01:22:50 GMT

So you want to ignore all selections EXCEPT for customer, and you specifically want period 3? I believe this:

sum({1<Customer=P(),Period={'3'}>} Units)

However, if ALL you wanted to ignore was the period, then just override the period:

sum({<Period={'3'}>} Units)

Set Analysis with 3 Fields

Fri, 19 Nov 2010 01:38:53 GMT

Thank you both but I'm still having trouble. This is exactly what I want: "ALL you wanted to ignore was the period, then just override the period:", however, when I use this formula and change the time period, the value goes to Zero unless I'm selected into Period 3.

Set Analysis with 3 Fields

Anonymous — Fri, 19 Nov 2010 02:57:33 GMT

If I got the requirements right:
sum({$<Period ={'3'}>} Units)

Set Analysis with 3 Fields

Fri, 19 Nov 2010 04:28:08 GMT

I have attached a .qvw to better help explain (the suggested answers are still not working). Thanks for your help everyone

Set Analysis with 3 Fields

Fri, 19 Nov 2010 04:34:57 GMT

It is easiest to reproduce the problem by selecting a PeriodDesc in the multibox.

Example: Blue, Last Month should yield 30, but the set analysis is returning 0.

Set Analysis with 3 Fields

johnw — Fri, 19 Nov 2010 18:06:55 GMT

You told us your field names were Period and Units. So I gave you this:

sum({<Period={'3'}>} Units)

And Michael gave you an equivalent (the $ is optional):

sum({$<Period ={'3'}>} Units)

In the example you posted, the field names are instead PeriodID and SalesUnit. You need to use your real field names in the expression, not the incorrect field names that you told us:

sum({<PeriodID={'3'}>} SalesUnit)

That gives the results you want.

Set Analysis with 3 Fields

Fri, 19 Nov 2010 18:25:48 GMT

sum({$<PeriodID ={'3'}>} SalesUnit)

^^ This is in the application I attached earlier in the main text box, center screen. I created this after my initial post so sorry if the naming threw you off.

Open the application and select Blue Customer and Last Month. Based on your equation, I should see "30" units, but I'm getting "0", no? What am I doing wrong?

Set Analysis with 3 Fields

johnw — Fri, 19 Nov 2010 19:57:05 GMT

Edit: Wait, OK, I see the 0 now, when you DO copy in the expression and do the selections you say, THEN it gets 0. It gets 0 because you are overriding the PeriodID, but making a selection in PeriodDesc. Since these fields are, I gather, the same thing, you ALSO need to tell it to ignore PeriodDesc.

sum({<PeriodID ={'3'},PeriodDesc=>} SalesUnit)

Set Analysis with 3 Fields

charlotte_qvw — Fri, 19 Nov 2010 21:31:38 GMT

Try this

sum({$<PeriodID ={'3'}, PeriodDesc = >} SalesUnit)

i.e. you could either set PeriodDesc to be ignored or you could also set it to LastWeek

Set Analysis with 3 Fields

johnw — Fri, 19 Nov 2010 21:53:17 GMT

The dollar sign is optional. I prefer to not include it since $ is also used in dollar sign expansion and to identify parameters, but I may be in the minority.

Set Analysis with 3 Fields

Fri, 19 Nov 2010 23:05:03 GMT

Thank you everyone - sorry if I confused anyone. Charlotte.qvw's answer fixed the problem