Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
Sorry but i dont understand your requirement.
Can you clarify?
Hi Marcel,
use rangesum (Below(AMOUNT_SOLD,0,3)) formula ..
I have done this Plz find the attachment..
See attached
Hi Marcel,
Plz see the attachment. It is correct as your requirement..
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
Hi
the task is to solve it in the SetAnalysis Function, not to change the data model.
Thx - Marcel
can you explain below?
What is your logic to receive below ?
So column3 of the pivot should contain values 600, 500 and 300.