Calculated Dimension

[#1]

Dear all,

I want to implement a bar chart that shows 3 bars with the following information:

- bar 1: total number of persons with heart rate above their ideal heart rate

- bar 2: number of persons with heart rate above their ideal heart rate for more than 200 days

- bar 3: number of persons with heart rate above their ideal heart rate between 50-150 days

In my data model I've the following dimensions:

- person

- date

- ideal heart rate (per day, per person)

- actual hear rate (per day, per person)

I've implemented the following table, where the totals (sum of rows) actually show what I want in the bar chart:

Dimension	Expressions
Person	# of days heart rate of person is above person's ideal heart rate	# of persons with heart above their ideal heart rate	More than 200 Days	Between 50-150 days
		6	5	1
37	6	1	0	1
40	19	1	1	0
53	55	1	1	0
74	18	1	1	0
100	54	1	1	0
140	29	1	1	0
31	0	0	0	0
42	0	0	0	0
96	0	0	0	0

The expressions are implemented as follows:

- # of days heart rate of person is above person's ideal heart rate

=sum(aggr(if(sum([Actual Heart Rate])-sum([Ideal Heart Rate])<0,1,0),[Date],[Person]))

- # of persons with heart above their ideal heart rate

=if(sum(aggr(if(sum([Actual Heart Rate])-sum([Ideal Heart Rate])<0,1,0),[Date],[Person]))>0,1,0)

- More than 200 Days

if([# of days heart rate of person is above person's ideal heart rate]>200,1,0)

- Between 50-150 days

=if([# of days heart rate of person is above person's ideal heart rate]>=5,if([# of days heart rate of person is above person's ideal heart rate]<=10,1,0),0)

Is this possible or is the above straight table my only option to show the information?

Kind regards,

James.

[#2]

Hi James,

I dont see there is a problem in having this data as a bar chart. Just try to change the chart type to bar chart...you can have mutiple expressions in a bar chart each appearing as a separate bar...let me know if you face any issue while doing this.

Thanks

Amit

[#3]

Hi Amit,

When I change it to a bar chart, I get for each person a bar. However, I want to show only 3 bars with the information from the totals.

Kind regards,

James.

[#4]

Hi James,

Do you mean to say that you want to display just the totals bar? If yes, remove the dimension from bar chart and keep only the expressions...it will give you 3 bars for the totals.

Thanks

Amit

[#5]

Hi Amit,

I've tried deleting the dimension, but the totals then are different. See this straight table:

Expression
# of days heart rate of person is above person's ideal heart rate	# of persons with heart above their ideal heart rate	More than 200 Days	Between 50-150 days
	1	1	0
181	1	1	0

Kind regards,

James.

[#6]

Hi James,

Could you attach a sample qvw file for this?

Regards,

Amit

[#7]

Hi Amit,

Sorry, but I can't upload a qvw sample. I hope you can help me in another way.

Kind regards,

James.

[#8]

Hi Amit,

I just uploaded a sample file. In this file the red bar should be of height 2 and not 1.

Kind regards,

James.

[#9]

Hi Amit,

I've just uploaded a sample file on my profile. In this report the red bar should be of height 2 and not 1.

Kind regards,

James.

[johnw]

Well, the sample file crashed out my QlikView, perhaps because I'm on 8.5. But basically, I think you want to use sum(aggr(...,Person)) to duplicate what your chart is doing. Stick your expressions in where the "..." is. Ah, I was able to bring it up in 9.0. Looks like it worked, assuming you wanted numbers 3 and 2.