4 Replies Latest reply: Mar 30, 2012 2:03 AM by Meelis Karulin

# Selection and variable, same source, different results?

Hello again.

Theres two listboxes containing campain name and sales, but one multiplies it by two for some reason.

Same campain, two different results. First sum is calculated like this:

=Sum(if(TransDate >=cBeginDate AND TransDate <=cEndDate ,Quantity))

and the second like this:

=Sum(if(TransDate >=\$(vcBeginDate) AND TransDate <=\$(vcEndDate) ,Quantity))

Variables vcBeginDate and vcEndDate are set from click action like this:

=if(GetPossibleCount(cBeginDate)=0,min(TransDate),cBeginDate)

and

=if(GetPossibleCount(cEndDate)=0,max(TransDate),cEndDate)

The first listbox name is calculated from selection and the second from variable, so they are both the same...

Then why is the result from the first listbox 2 times greater?

I can use /2 on the first one and get a correct answer but its impractical and i want to understand why it's doing that

• ###### Selection and variable, same source, different results?

Is there a relation between TransDate and cBeginDate in the data model?

Karl

• ###### Selection and variable, same source, different results?

Yes, through Product code. There is a synthetic table in the equasion because one table had two common fields with rest of the tables, TransDate and ProductCode.

• ###### Selection and variable, same source, different results?

When you use the variable you are not considering the relationship between cBeginDate and TransDate so you compare 1 cBeginDate with your TransDate.  If you use the field, the problem might be that there are 2 cBeginDate related to ProductCode so QV is confused and double counts the number of transactions that are less than cBeginDate.

One way to fix this is to assign a Campaign ID code in the Sales table so that you would have 1-to-1 relationship between cBeginDate and TransDate.

Karl

• ###### Selection and variable, same source, different results?

Thanks Karl. It indeed occurs only with products that have more then one cBeginDate.