How to use string content of field in SetAnalysis function?

[hugmarcel]

Hi

see test.qvw attached.

having data:

// Data

BOOKINGS:
LOAD * INLINE [
PRODUCT, AMOUNT_SOLD, TARGET_PRODUCTS,
Product 1, 100, '"Product 1","Product 2","Product 3"'
Product 2, 200, '"Product 2","Product 3"'
Product 3, 300, '"Product 3"'
];

//Goal: Show this Pivot:
//PRODUCT, sum(AMOUNT_SOLD), sum(AMOUNT_SOLD) for TARGET_PRODUCTS
//Product 1, 100, 600 //100 + 200 + 300
//Product 2, 200, 500 //200 + 300
//Product 3, 300, 300 //300

//What is the correct formular for column 3 "sum(AMOUNT_SOLD) for TARGET_PRODUCTS"?

thx - Marcel

[MK_QSL]

Use this in SCRIPT... what is your requirements?

BOOKINGS:

LOAD

  PRODUCT,

  AMOUNT_SOLD,

  Replace(SubField([TARGET_PRODUCTS],','),'"','') as [TARGET_PRODUCTS]

INLINE

[

  PRODUCT, AMOUNT_SOLD, TARGET_PRODUCTS,

  Product 1, 100, '"Product 1","Product 2","Product 3"'

  Product 2, 200, '"Product 2","Product 3"'

  Product 3, 300, '"Product 3"'

];

UPDATE : FILE ENCLOSED.

[hugmarcel]

I do not want to have the products listed, but the amounts calculated.

So column3 of the pivot should contain values 600, 500 and 300.

Thx - Marcel

[MK_QSL]

Sorry but i dont understand your requirement.

Can you clarify?

[Anonymous]

Hi Marcel,

use rangesum (Below(AMOUNT_SOLD,0,3)) formula ..

I have done this Plz find the attachment..

[giakoum]

See attached

[Anonymous]

Hi Marcel,

Plz see the attachment. It is correct as your requirement..

[hugmarcel]

Hi

rangesum (Below(AMOUNT_SOLD,0,3)) is not generally correct, as it uses the order of the products for calculation, but not the target_product string in the table.

To have a general solution, the target_product data must be part of the formula.

Thx - Marcel

[hugmarcel]

Hi

the task is to solve it in the SetAnalysis Function, not to change the data model.

Thx - Marcel

[MK_QSL]

can you explain below?

What is your logic  to receive below ?

So column3 of the pivot should contain values 600, 500 and 300.