Selection and variable, same source, different results?

[#1]

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

Thanks n advance

[pover]

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

Karl

[#3]

Thanks for quick reply Karl,

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.

[pover]

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

[#5]

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