Sum aggr if and filter select

[Qliksense_77]

Hi,

I have data like below.

I am using the expression below for the following:

1. When b and c are on the same date and name then count c

2. When a,c or d are on the same date and name then count c.

I get the desired outcome from the expression if no names are selected in the filter..

But for instans if name "a" is selected, then I get a count for id 5. There should not be a count for id 5 because of rule number 2.

I hope somebody can help:)

=sum(aggr(
if(index(concat (distinct total <Id,Date> Name),'b')>0 and
index(concat (distinct total <Id,Date> Name),'c')>0,sum({<Name={'c'}>}Value),
(if(index(concat (distinct total <Id,Date> Name),'a')>0 and
index(concat (distinct total <Id,Date> Name),'c')>0
or index(concat (distinct total <Id,Date> Name),'d')>0
,sum({<Name={'c'}>}Value)
,count (Distinct Value)))),
Id, Date, Name))

Id	Date	Name	Value
			
1	10-10-2020	a	1
2	11-10-2020	b	1
3	12-10-2020	b	1
3	12-10-2020	c	1
4	13-10-2020	a	1
4	13-10-2020	b	1
5	13-10-2020	d	1
5	13-10-2020	a	1
5	13-10-2020	c	1

[chrismarlow]

Hi,

When you select a in the filter I think you are just seeing the Count(Distinct Value) that is your default as neither of your sets of conditions apply.

I say that as if I alter your expression to multiply that bit (the count) by 100 to make it stand out I see the following;

What are you expecting to see when you select a?

Cheers,

Chris.

[Qliksense_77]

Hi @chrismarlow ,

When I select a, then I want a count of the a for Id1 (row 1) and the for Id4 (row 5).

The count below should be the same no matter the selection in filter. The a for id5 should not be counted.

Id	Name	"-=sum(aggr(
if(index(concat (distinct total <Id,Date> Name),'b')>0 and
index(concat (distinct total <Id,Date> Name),'c')>0,sum({<Name={'c'}>}Value),
(if(index(concat (distinct total <Id,Date> Name),'a')>0 and
index(concat (distinct total <Id,Date> Name),'c')>0
or index(concat (distinct total <Id,Date> Name),'d')>0
,sum({<Name={'c'}>}Value)
,count (Distinct Value)))),
Id, Date, Name))


"
1	a	1
2	b	1
3	c	1
3	b	0
4	a	1
4	b	1
5	c	1
5	a	0
5	d	0