count distinct sessionid for those session which have not sold any item for an category

[#1]

I have the follow set of data, i would like to show at a bar chart:

x-axis: category

y axis: count distinct session id for those session which have not sold any item for the category.

with the following set of data, the data point value should be as follow:

E:3

DB:2

DL:2

DE:2

JL:2

Q:2

R:1

W:1

I have test with the expression:

=Count(DISTINCT{<Flag = {1},[Session ID] = p({<Flag = {1}>})*e( {<Flag = {0}>})>}[Session ID])

but the bar show 2 for all category, and only if i selected an category from the list box the values show is correct.

[tresesco]

Though I am not sure what you are trying here. However, it seems that, your expression doesn't work the way you want because set analysis expression gets evaluated once for the entire chart and NOT individually for chart dimension values.

[effinty2112 · Accepted solution]

Hi Sara,

Try:

Category	=Count(Aggr(DISTINCT if(Flag = 1,[Session ID]),[Session ID],Category))
DB	2
DE	2
DL	2
E	3
JL	2
Q	2
R	1
W	1

Cheers

Andrew

[#4]

Thanks Andrew!

It gives the wanted results.

[effinty2112]

Nice!! Glad to help!

[#6]

Hi Andrew,

May i ask a question about the expression:

=Count(aggr(distinct if(Flag=1, [Session ID]),[Session ID],Category))

for example for the E category,

just by looking at the expression it seems to help count the distinct Session_ID which the category exists and have flag=1, i am not sure how adding the if statement "if(Flag=1, [Session ID])," helps to filter out those session with the same category have both Flag=0 and Flag=1 in the session...

for example the E gives the value 3, which i believe it is counted from session 2222, 3333, and 3620...and session 4444 and 5555 is not counted because it have the E category having both Flag=1 and Flag=0...

Could you kindly help to clear up my confusion with this..

[effinty2112]

Hi Sara,

The more I look at this the harder I find it to explain so if you don't mind can I replace that expression with one that is easier to understand and for me to explain? I wish I had given this one to you instead:

Count(DISTINCT [Session ID])-Count({$<Flag={0}>}DISTINCT [Session ID])

Count of distinct sessions minus the distinct count of those that had at least one flag =0

Category	Count(DISTINCT [Session ID])-Count({$<Flag={0}>}DISTINCT [Session ID])
DB	2
DE	2
DL	2
E	3
JL	2
Q	2
R	1
W	1

Hope this is okay for you

Regards

Andrew

[#8]

Andrew,

Very clear and thank you so~much for all your trouble to explain this to me.

I understand now, thanks again...