Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hello community,
I've got a problem with a set analysis.
I'd like to get all records of companies which use the combination of 2 products, NOT only one of the products.
Example (does not work I know):
1. product:
=sum({<articleNr={111}>} numberOfLicences)
2. product:
=sum({<articleNr={222}>} numberOfLicences)
As information, my data model looks like this:
company as dimension table
calendar as dimension table
products as transaction table
Does anyone know how to solve this?
Thanks in advance!
Regards,
Carl
i dont understand what you reuired
but see below for more clear
sum({<A={222},B={'somvalue'}>} fieldname) -------------------> it will give you result where a=222 and B='Some value'
or is like below
sum({<A={222,322}>} fieldname)--------------------------------------> if A=222 or A=322 then it sum the value
or you may also try below
=sum({<articleNr={111}>} numberOfLicences)
+
sum({<articleNr={222}>} numberOfLicences)
Have had the same way of thinking, know that one field can't have two values at the same time
Thought that there is perhaps a way to solve this, perhaps if i change my data model, my problem is, that i dont know how ...
Hi,
Use the union between the two.
Operator Operator name desc
+ Union Returns a set of records that belongs to union of sets.
Hi
Perhaps the problem is this: Since set analysis is effectively a way of coding selections, whatever you are selecting with set analysis must also be manually selectable. Now for AND selections in a list box, the data needs to be structured in a particular way. Once you have done, this then perhaps one of the proposals above will work.
Search here AND LISTBOX or similar, or check the manual for how to structure your data.
HTH
Jonathan
May be in the fron-end too you can achieve that. Could you explain better about your requirement ?
With SET ANALYSIS this could be calculated like this:
Sum({<Company=P({<Product={'A'}>})>*<Company=P({<Product={'B'}>}),Product={'A','B'}>} numberOfLicenses)
(this will only be calculated for companies with transactions of both product A and B and it will only sum licenses
of A or B).
Or
Sum({<Company=P({<Product={'A'}>})>*<Company=P({<Product={'B'}>})>} numberOfLicenses)
(this will only be calculated for companies with transactions of both product A and B and it will sum all kinds of licenses for these companies).
Note that the asterisk in a SET ANALYSIS is the set operator INTERSECTION.. So basically by using the
P() element function which selects all companies that have transactions for product A and intersect that with companies that have transactions for product B you will get only companies that have transactions with both products. In addition when you do the sum make sure to specify that you only want sums for these two products... if that is what you need in your chart....
I will also upload a complete example QVW to illustrate even better....
!
Actually the expression could be simplified somewhat to this:
Sum({<Company=P({<Product={'A'}>})*P({<Product={'B'}>}),Product={'A','B'}>} Licenses)