Query calcualtion with condition

[b737]

I have in my rows Retailers (Retailer1,2,3,....), in columns I have Products (Product1,2,3,...), and measures are number of sold products by retailers. Now I want to crate new query calculation which sum number of Retailers who have sold Product 1 (as must, condition) and some of other products (Product 2,5...), where is not obligated of which product number.

Here how it looks:

                   PR1   PR2   PR3  ...   New Query Calculation
Retailer1        1       1      1          1       
Retailer2        1       1      1          1   
Retailer3                 1      1                                   
......
Sum                                            2 - number of RT who have sold PR1 and some other product

Thank you very much in advance

[RKMI]

Is this Dimensional or Relational model?

[b737]

This is dimensional reporting

[RKMI]

If you are pulling a member set of products then you can get Product 1,2,3,4. Then you spefically pull another set Product 1,2,5 or then that will be consolidated. Your quantity sold measure nested under first set then for the second set just do a tuple([QTY sold], [Retailer1]) where [Retailer1] is member. This way you will only get the qty sold for retailer1 for the second product set.

Thanks,
RK

[b737]

Since I am new in Cognos, I will need help in syntax, is it like this to type in new query calculation

tuple([QTY sold], [Retailer1]) where [Retailer1] is member)

Thank you

[MFGF]

Hi,

No - far more simple

tuple([QTY sold],[Retailer 1 member])

In the above expression, [QTY sold] is your measure and [Retailer 1 member] is the relevant Retailer member from the package.

Cheers!

MF.

[RKMI]

Sorry, I should've put the syntax in code tags. Anyways hope that works out.

Thanks,
RK

[b737]

Thanks it works great.

Also I have  Relational model, which is similar like this. Is it possible to do this whit that model and how.

Thank you, and I hope that I am not asking to much  8)

[RKMI]

Hi,

Using a case statement you can achieve it, Create a crosstab bring in Retailer for rows, Product in columns then nest QTY sold under products. After which you need to create a data item (Selected) which is Product 1,2,5 so,

Code Select Expand

case when ([Product] in (1,2,5)) then ('Selected Products')
end

Here is what QTY sold for specified product in retailer data item ( QTY Sold for selected) would look like.

Code Select Expand


case when (([Product] in (1,2,5)) and ([Retailer] = 1) ) then ([Qty Sold])
End


Then drag in Selected Data item nest to Products, and QTY Sold for selected under.

This should replicate the Dimensional behaviour. Hope this makes sense.

Thank,
RK