topic Selection and variable, same source, different results? in QlikView

Selection and variable, same source, different results?

Wed, 28 Mar 2012 20:13:26 GMT

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

Selection and variable, same source, different results?

pover — Wed, 28 Mar 2012 21:31:26 GMT

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

Karl

Selection and variable, same source, different results?

Thu, 29 Mar 2012 05:44:12 GMT

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.

Selection and variable, same source, different results?

pover — Thu, 29 Mar 2012 15:05:04 GMT

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?

Fri, 30 Mar 2012 06:03:16 GMT

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