14 Replies Latest reply: Nov 19, 2010 6:05 PM by Michael_T

# Set Analysis with 3 Fields

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

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

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

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

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

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

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

• ###### Set Analysis with 3 Fields

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

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

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

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

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

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

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

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