Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I want to have a count on a concat-function.
Person: Concat({< Date_Last24Hours = {1}>} DISTINCT Product):
A, 1,
B, 1;2,
C, 1,
D, 2,
E, 1;2,
F, 1
G, 2
Where Date_Last24Hours gets the products of the persons in the last 24 hours. I want to show, in a text object, how much persons are using only Product 1 in the last 24 hours (answer is 3) or how much are using both 1 and 2 in the last 24 hours (answer is 2 because 1;2 is used twice). So for each question I want a separate text object.
Anyway to get there?
May be function Count(), not Concat()?
Hi Andrey, thanks for your time. That is not working as I want to count all specific results of the concat. So, the real list I am loading into Qlikview is:
Person Product
A, 1,
B, 1,
B, 2,
C, 1,
D, 2,
E, 1;
E. 2,
F, 1
G, 2
And by Using the concat I get all products of the persons. I am interested in persons who are using product 1 and product 2.
for product 1
Count({<Product={'*1*'} > } Distinct Person)
for Product 2
Count({<Product={'*2*'} > } Distinct Person)
Hi Vineeth,
Thanks. However, I am interested in 3 cases:
Persons who only use Product 1 (Person A, B and F so answer is 3)
Persons who only use Product 2 (Person D and G so answer is 2)
Persons who use Product 1 AND Product 2 (Person C and E, so answer is 2)
With your option I get the total count which gives a result of 5 for Product 1 and 4 for Product 2. That's the reason I already used a concat to get all possible combiantions.
Persons who only use Product 1 (Person A, B and F so answer is 3)
=count({< Person = e({<Product -={1}>}Person) >} distinct Person)
Persons who only use Product 2 (Person D and G so answer is 2)
=count({< Person = e({<Product -={2}>}Person) >} distinct Person)
Persons who use Product 1 AND Product 2 (Person C and E, so answer is 2)
=count({< Person ={"=count({<Product={1,2}>}distinct Product)=2"} >} distinct Person)
Create a straight table with dimension
=Aggr( Concat({< Date_Last24Hours = {1}>} DISTINCT Product), Person)
and a single expression
=Count(DISTINCT Person)
Hi,
Please see below and qvw attached. I hope this helps you.