Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi QlikView Masters
I'm trying to calculate some economic effects in a function that would take data for only newly introduced products. For example:
Year 2010, Products: A, B
Year 2011, Products: A, B, C
Year 2012, Products: A, B, C, D, E
I need to have variables or a table that will contain a sum of sales only for the new product(s), so there should be:
New_Product_Effect_2011: sum of sales of C
New_Product_Effect_2012: sum of sales of D and E
or a table instead of two variables here.
In SQL I would write:
SELECT SUM(Sale_Value) FROM SALES_TABLE
WHERE Year = 2011 AND Product_id NOT IN (
SELECT Product_id FROM SALES_TABLE
WHERE Year = 2010
)
But I don't know how to achieve it in QV logic without using hardcoded products names. Also, is there a way to get something like this in QV:
SELECT Product_Name, SUM(Sale_Value) FROM SALES_TABLE
WHERE Year = 2011 AND Product_id NOT IN (
SELECT Product_id FROM SALES_TABLE
WHERE Year = 2010
)
GROUP BY Product_Name
?
I'm attaching my sample files, but my current solution (separate variables for each year) it is not necessarily the best way for this purpose. I would be very happy to have at least the total sales value only for new products in a year or possibly the sales for each of new products in a year.
Many thanks in advance for any help...
Best regards,
Paweł
See attached example
Thanks so much for answering so quick ans I'm ashamed for answering you just now. I'm not that good with set analysis yet and it's a bit complicated but it suits my needs - thanks a lot!