Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Is there a relation between TransDate and cBeginDate in the data model?
Karl
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.
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
Thanks Karl. It indeed occurs only with products that have more then one cBeginDate.