How to Calculate one field against the a value in other field

[rachelpurple]

Hi,

Say I have table like this.

Host	Event	Proposed Event
1		A
2		A
3	A	A
4	A	A
5		B
6	B	B
7		C
8	C	C
9	C	C
10	D	D
11	D	D

I want to create a bar chart having Event and Proposed Even as x-Axis, count of Host as y-Axis, so it will show

A Proposed: 4

A Event: 2

B Proposed: 2

B Event: 1

C Proposed: 3

C Event: 2

D Proposed: 2

D Event: 2

I now have Proposed Event as my dimension, and how to build the expression to realise this?

Guess this is to do with calculate Host field against the value in Proposed Event and in Event respectively?

Thanks,

Luda

[sunny_talwar · Accepted solution]

Like this?

Dimension

Proposed Event

Expressions

=Count({<Host = {"=Len(Trim([Proposed Event])) > 0"}>} Host)

=Count({<Host = {"=Len(Trim(Event)) > 0"}>}Host)

[rachelpurple]

Thanks a lot. I believe it should work

However, actually my table is like this:

Host	Event	Proposed Event
1	E	A
2	F	A
3	A	A
4	A	A
5	E	B
6	B	B
7	E	C
8	C	C
9	C	C
10	D	D
11	D	D

So I tried your expression it doesn't work on mine.

Just that I need to change anything in Event but not in Proposed Event to "0" or Blank first.

Do you know how to change that? or if you know how to realise the chart without changing this?

[sunny_talwar]

May be this for Event:

=Count({<Host = {"=Event = [Proposed Event]" }>}Host)

[rachelpurple]

Thanks very much Sunny,

This works!

[sunny_talwar]

Awesome

Please close this thread by marking correct answer.

Qlik Community Tip: Marking Replies as Correct or Helpful‌

Best,

Sunny