Intersection of List box values

[#1]

Hi,

I've a table with the details of Product a company makes..A,B,C,D etc.....

Now in a particular yearmonth the company used to make only Product A & B

& gradually it started making C,D, E etc...& stopped making A etc.

now i want to have two selection boxes for YearMonth- to compare over the two selected periods which are the new products for the latest YearMonth which were not made in the older selected YearMonth.

For this, i've taken a resident load of the YearMonth & Product fields from the main table to create two separate list boxes.

Now i've YearMonth1 & Products 1 from main table; &

             YearMonth 2 & Products 2 from resident table load

if i make selections in the two YearMonth boxes as desired i get the correct Product list in the Products List boxes

Problem: But now i need a list of those products which are present in Product 1 but not in Product 2 & vice-versa also

i.e intersection of the two list boxes.

Plz help

Regards

[swuehl]

The only() function in the chart expressions will return NULL for those products. I've set the NULL symbol in presentation tab of the chart properties accordingly.

[#3]

Dear Sir,

want to bother u once more...hope u dnt mind

Sir, in the expression :

if(Common,only({<Product /= p(Product2),Category=,Type=,Month=p(Month2) >} Category))

for IN products does the use of Month=p(Month2) mean that Month equals all the possible values of Month2 for current selections. Instead of that shouldn't we use simply Month=Month2 so that Month takes only the currently selected Month2 value.

Please help me understand the difference beween the two.

Regards

[swuehl]

You are right, that p(Month2) will use all possible values of Month2 for the Month field modifier. In the case that you have selected in Month2 field, this is the same than using Month= Month2, which will only take the selected values.

So, you are right, it might be better to use Month=Month2.

I think your chart will probably only show meaningful values when there is a selection of exactely one value in Month and Month2, so you migth want to enforce that by enabling the 'Always one selected value' option in the list box properties or by setting a calculation condition in the general tab of your table charts, like

GetSelectedCount(Month)=1

[#5]

Dear Sir,

Thanks for the answer.

At least i'm able to understand something now under ur guidance.

really encouraging to know from u : "So, you are right, it might be better to use Month=Month2."

will also try what u've suggested

Thanks & Regards

[#6]

Dear Sir,

Continuing with the question of Category & Type.

Next i need to create another table:

Selections Month & Month2 as before (Month >Month2), which shows the products who were present in both the periods but whose Category & Type have changed.

So, it should have columns: Product, OldCategory, OldType, NewCategory, NewType

I've tried using Product as dimension and only({<Month=Month2>} Category)  as expr1,only({<Month=Month2>} Type) as expr2, only({<Month2=Month>} Category) as expr3,only({<Month2=Month>} Type) as expr4

(please note that i've already selected Month & Month2). But this gives me those products also whose Category & Type have remain unchanged.

To resolve this, i created a key Product &'-'& Category &'-'& Type in both the tables (main table for Month & derived table for Month2) so that i can eliminate those keys which are same for both the Period selections.

But i don't know which operator to use as * will return the matching products & / will return the products present in either of them. I've seen a Exclusion operator in the help section. I've used it but not sure is it correct.

Please help

Regards

[#7]

Dear Sir,

After using the calculation suggested by you in my application for IN & OUT sheets, i'm getting sum 2 for the expression sum({<Product /= p(Product2)>}1) for every product.

Also, i'm not getting all the IN & OUT products. I would like to tell you that my data model concatenates data from qvd & excel sheet, the data for Months May & June come from the qvd while for previous months it comes from an Excel sheet.

But when i export all the data from a straight table/table box from QV to an excel sheet and import that excel sheet in QV then i get sum 1 for the expr sum({<Product /= p(Product2)>}1) as well as all the correct IN & OUT products.

Sir, could you please point out problem in my original data model.

Best Regards

[swuehl]

I don't have much time to look into this this week.

Maybe you want to open a new thread, thus getting more attention to your problem.