topic Calculated Dimension in QlikView

Calculated Dimension

Mon, 03 Aug 2009 17:53:36 GMT

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.

Calculated Dimension

Mon, 03 Aug 2009 18:25:21 GMT

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

Calculated Dimension

Mon, 03 Aug 2009 18:32:44 GMT

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.

Calculated Dimension

Mon, 03 Aug 2009 18:40:26 GMT

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

Calculated Dimension

Mon, 03 Aug 2009 19:17:05 GMT

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.

Calculated Dimension

Mon, 03 Aug 2009 19:52:53 GMT

Hi James,

Could you attach a sample qvw file for this?

Regards,

Amit

Calculated Dimension

Mon, 03 Aug 2009 20:54:05 GMT

Hi Amit,

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

Kind regards,

James.

Calculated Dimension

Wed, 05 Aug 2009 20:56:30 GMT

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.

Calculated Dimension

Wed, 05 Aug 2009 20:59:00 GMT

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.

Calculated Dimension

johnw — Sat, 08 Aug 2009 05:42:40 GMT

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.