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
Can you provide little sample data with required output?
EDIT::
=COUNT({<articleNr = {111}, articleNr = {222}>}DISTINCT numberOfLicences)
Or
=COUNT({<articleNr = {111}, articleNr = {222}>}DISTINCT numberOfLicences)
comma between two expression worked as and
for example
sum({<A={222},B={'somvalue'}>} fieldname)
or is like below
sum({<A={222,322}>} fieldname)
Hi Carl,
I dont' know, wheter i am getting it correct or not. but what i understand is that you required articleNr 111, and 222 with number of licences. If it is so then use below .
=sum({<articleNr={111,222}>} numberOfLicences)
Hi,
Try like this in expression
=Sum(Aggr(If(Count(Distinct articleNr) > 1, Sum(Measure)), Customer))
Regards,
Jagan.
hi,
thanks for your answer but this is not working.
Using a comma separation ={'111','222'} will make an OR rather than an AND, I think. Using ={'111'}, ={'222'} forces an AND.
this is not working, if i do it like that, i get all companies which have 222 OR 322, but i Need those which have 222 AND 322
Let me know the edited version working or not...if not working, kindly provide your sample data..
You are right. But what you are thinking of - is not realistic. At the same time a single field(for you - articleNr) can't have two values. So AND logic is illogical here. What do you say?