Set Expression Question

[#1]

appologies if youve seen this question before but (im having a problem with Message Posting timeouts)

This set function is supposed to sum price*Qty for current Financial Week's (FWeek). FWeek is a field in a list Box That can filter more than one week. the set Calc works when filtered to current week but not more than 1 Week.

How does it need to change to sum up ALL selected Weeks ?

sum({1<FWeek = {$(=FWeek)} } >} (PLU_PRICE*QUANTITY))

ColinR

[#2]

Hi Colinr,

Why do you need a set analysis for calculating a sum of Plu_price * Quantity? if you wanna get the result for the selecting week just write the sentence sum(Plu_price * Quantity) and it will work for one or more than one week selected.

I hope this help you.

Best regards.

[#3]

It doesnt,

This is a simplified extract of my problem, I have a more complex filter Appliied im using the set expression to break out of the Current Filter and do a calc of PLUPrice*Qty using only the Weeks selected portion of the applied Selections

colinr

[#4]

First, you should put that expression into a chart, but don't give it a label. Then when the chart is rendered, the label will be your Set Analysis expression with your dollar sign expansion evaluated.

What is the format of FWeek values? You may just need quotes around the dollar sign expansion:

sum({1<FWeek = {"$(=FWeek)"} } >} (PLU_PRICE*QUANTITY))

If your weeks are string values, the dollar sign expansion is going to give you:

"Value1,Value2,Value3"

You need it to be:

"Value1",Value2","Value3"

If that's the case, then you probably need to use a variable.

[#5]

Nice Tip, But in My case selecting 1 item returns Single Value Double Quoted as expected, Selecting More than 1 Item returns "-" the Null Symbol

My Version is 9 Sr2

Is there a better Way than $Expansion, Im Guessing {} is an Array but {FWeek} doesnt Work and this must be fairly common requirement ?

ColinR

ColinR

[#6]

Weird, so it sounds like you are getting the equivilent of ONLY(FWeek) when using $(=FWeek).

Try creating a varible. Set the value to:

=GetFieldSelections(FWeek)

That will return a comma-delimited list of all selected items. Then in your expression, use:

sum({1<FWeek = {$(vWeek)} } >} (PLU_PRICE*QUANTITY))

Where vWeek is the name of the variable defined above. Again, if you need the quotes around multiple values, you'll need to modify the variable to provide it.