Set Analysis with 3 Fields

[#1]

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.

[Miguel_Angel_Baeyens]

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.

[#3]

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?

[Miguel_Angel_Baeyens]

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

[johnw]

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)

[#6]

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.

[Anonymous]

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

[#8]

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

[#9]

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.

[johnw]

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.