possible fieldvalues in set analysis

[#1]

Hi,

is it possible to set your possible fieldvalues into a set analysis?

Like: if I select the month october, the possible fieldvalues for week are: 40, 41, 42,43 and 44.

So if I want to make a sum of sales for all week that are possible in october, even if the date itself aint in october

I would have something like

SUM(
{<week = {40, 41, 42, 43, 44}>}
Sales)

If I change the month, the possible values also change, so I cant put the values hardcoded in my statement

Is this possible? if yes how?

Thanks in advance

Rey-man

[#2]

I think in order to use the Possible Selections, you'll probably need a macro. You can set up a macro to fire when there is a selection on any field. Then you can loop through the possible selections in your desired field and write those values to a variable.

You can then use the variable inside your Set Analysis with a dollar sign expansion:

Sum({<week ={$(vPossValues)}>} Sales)

QlikView is acting up for me right now, so I can't look up the Possible Values syntax. You should be able to find an example in the API Guide and I don't think you would need anything more complicated than the sample.

[Oleg_Troyansky]

If your month is selected, then the simple aggregation will already limit the data to the possible weeks.There is no need in Set Analysis.

If your month is not selected, but you want to use it in Set Analysis, then you can either use a Set Analysis condition on the Month, or use the new Set Analysis function P(), and specify the Month condition there (you need to be using ver. 9.00 for that).

Finally, if I'm missing something and you really do need to reference possible values in Set Analysis - use function Concat() to construct the list of possible values, delimited by comma, and include that list in your Set Analysis condition.

cheers,

[#4]

I think I didn't explain myself enough.

I've got a calendar with a date_key

I've got a deleverytable with the date_key.

That date_key is linked to a week in that table. (deliver week)

But there is also an week in which the product is wanted.

When I select a week from the calendar, it should give me the sum of sold items in which the delivery week is (thats done automaticly, but only for those products who are also wanted in that week (second week field).

Example:

I select the weeks 42 till 44. My table contains:

Salesdoc date_key deliver week wanted week amount

1 2009-10-20 43 43 10

2 2009-10-20 43 42 20

3 2009-10-21 43 45 30

4 2009-10-22 43 37 40

5 2009-10-23 43 40 50

What i want is, that the only the values that are linked (deliver week) and the wanted week thats equal to 42 till 44.

So It should give me

Salesdoc date_key deliver week wanted week amount

1 2009-10-20 43 43 10

2 2009-10-20 43 42 20

Cause the rest ain't within the selection.

Hope this is clear.

Rgds,

Rey-man

[#5 · Accepted solution]

You're making the selection on Deliver Week, but you also want that selection to apply to Wanted Week?

Try:

Sum({<[Wanted Week] ={$(=GetFieldSelections([Deliver Week]))}>} Sales)

I think there is actually an easier way to do this. According to the help (from 8.5), e.g. <OrderDate = DeliveryDate>. This modifier will take the selected values from DeliveryDate and apply those as a selection on OrderDate.

That means, this should work for you:

Sum({<[Wanted Week] = [Deliver Week]>} Sales)

Unfortunately, I haven't been able to get that to work in practice (but I can't say I spent that much time trying).

[#6]

Hi NMiller,

I found that myself. The only thing I had to do in order to get it working, was to change the week selection (based on the calendar) to the week in the table

so that the

[Wanted Week] = [Deliver Week] could take place. Because if nothing was selected, the result is empty.

But your solution is good.

Rey-man