cancel
Showing results for
Did you mean:
Not applicable

## 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.

1 Solution

Accepted Solutions
Partner - Creator

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

14 Replies
Employee

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.

Not applicable
Author

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?

Employee

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

Champion III

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)

Not applicable
Author

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
Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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.

Champion III

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.

Community Browser