Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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
charlotte_qvw
Contributor

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

14 Replies
MVP
MVP

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.

Not applicable

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?

MVP
MVP

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

MVP
MVP

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)

Not applicable

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.

mov
Esteemed Contributor III

Set Analysis with 3 Fields

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

Not applicable

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

Not applicable

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.

MVP
MVP

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.

Community Browser