set analysis syntax

[Anonymous]

I have difficulties understanding the syntax of set analysis. What does the ' = ' exactly do?

some information:

vShipDate is a variable based on a selection in EDCDate: = only(EDCDate)

all date formats are yyymmdd (eg: 20170911)

why do those formulas give me different results?

=sum({<ExpectedShipDate={'>$(vShipDate)'}, ErsterfassungsDatum={'=$(vShipDate)'}>}BypackQuantity) = 136.892

=sum({<ExpectedShipDate={'>$(vShipDate)'}, ErsterfassungsDatum={'$(vShipDate)'}>}BypackQuantity) = 124.725

I only want to sum quantities which fulfill both conditions.

thanks,

Lucas

[MarcoWedel]

maybe helpful:

The Little Equals Sign

The Magic of Variables

The Magic of Dollar Expansions

regards

Marco

[Anonymous]

hi Colin

Does your data actually have different BypackQuantaties against different ExpectedShipDates for the same ErsterfassungsDatum value?

what do you mean with different BypackQuantities? yes there is more than one value with the same ErsterfassungsDatum for different ExpectedShipDates.

no, the columns have different values, see screenshot

[Colin-Albert]

Hi Lucas,

Your screen-shots with a blabk column label show the expression details.

Try adding columns with the expressions

     sum({<ExpectedShipDate={'>$(vShipDate)'}, ErsterfassungsDatum={'=$(vShipDate)'}>}BypackQuantity)

and

     sum({<ExpectedShipDate={'>$(vShipDate)'}, ErsterfassungsDatum={'$(vShipDate)'}>}BypackQuantity)

with blank labels, and you will be able to see how the actual expressions differ when the variables are expanded.

[Anonymous]

Colin,

that was exactly my point

I see the difference but i don't understand it.

first one is

=sum({<ExpectedShipDate={'>$(vShipDate)'}, ErsterfassungsDatum={'=$(vShipDate)'}>}BypackQuantity)

and second is

=sum({<ExpectedShipDate={'>$(vShipDate)'}, ErsterfassungsDatum={'$(vShipDate)'}>}BypackQuantity)

[Colin-Albert]

Your top query using '=20170911' includes values that do not match that date. The first row is 20170908.

Do your results change if you replace the single quotes with double quotes?

Quotes in Set Analysis

[Anonymous]

unfortunately Im stuck at SR3 because I cant update my QV myself. So i got to work with what I got.

So the blog tries to tell me, that search strings like mine  " ErsterfassungsDatum={'$(vShipDate)'}"

should actually put into double quotes.

I tried this but it does not show any different results

but you are right, since the top query is showing results that do not match the string, the syntax must be wrong?!?!

[Colin-Albert]

Hi Lucas,

The search string should be ErsterfassungsDatum={"$(vShipDate)"} though the change does not take effect until the Nov2017 release.

I have seen issues when dates are formatted as 2017/09/11 where the equals sign forces the date to be evaluated as 2017 divided by 9 divided by 11 = 20.373737, but this is not the case with your date.

Set analysis is not evaluated line by line so there may be an issue with your expression of only(EDCDate) returning null.  Only() returns a value if there is a single value in the field otherwise it returns null. I prefer to use maxstring() or minstring() rather than only() in set expressions as the max functions do not return nulls unless the whole column is null.

[Anonymous]

yes I tried

ErsterfassungsDatum={"=$(vShipDate)"}

and

ErsterfassungsDatum={"$(vShipDate)"}

but the results did not change

I think it has to do with my table structure and DateKeys with the MasterCalendar. But this would take more time to evaluate.