topic Restricting Duplicates in Chart Expressions in QlikView

Restricting Duplicates in Chart Expressions

Mon, 12 Apr 2010 08:17:16 GMT

Hi My table contains fields ->RegisterId ->StartDate ->EndDate I have duplicates in RegisterID In Layout, I have Guage chart which shows the average of (EndDate-StartDate) Since RegisterId contains duplicates ,Average showing is wrong. I dnt want to remove duplicates when loading my table. I just want to restrict duplicates in Gauge chart expression. Existing Formula is avg(EndDate-StartDate) Any Suggestions?

Restricting Duplicates in Chart Expressions

Mon, 12 Apr 2010 13:40:15 GMT

Hi,

Can anyone help me on this?

Restricting Duplicates in Chart Expressions

Anonymous — Mon, 12 Apr 2010 15:28:00 GMT

Well, what is your definition of average? What is "wrong" and what is 'right"? For example, you have the data like this:

RegID End Start Difference
1 20 10 10
1 50 30 20

Now, the average will be 15 - is it wrong? What is the expectaion here? I think as soon you figure out what you want to see, you'll figure out how to do it.

Restricting Duplicates in Chart Expressions

Tue, 13 Apr 2010 06:25:36 GMT

Sample Data

RegID Startdate Enddate difference

1 2-12-2009 5-12-2009 3

2 1-12-2009 7-12-2009 6

Avg->15/4=3.75

But actual avg->9/4.5=4.5

This is the problem I am facing in Gauge Chart.

I dont want to remove duplicates in loading .i just want to touch layout.Any ways?

Restricting Duplicates in Chart Expressions

pover — Tue, 13 Apr 2010 06:59:41 GMT

Hi Blessy,

Try the following expression:

avg(aggr(EndDate-StartDate,RegID))

Regards

Restricting Duplicates in Chart Expressions

Tue, 13 Apr 2010 07:08:07 GMT

Thanks Karl..

It worked ...