topic averaging values using multiple aggregation fields in QlikView

averaging values using multiple aggregation fields

Thu, 16 Dec 2010 21:01:44 GMT

Hello,

I have a table like this:

ID Date-Time Value

1 12/10/2010 08:00:00 AM 10

1 12/10/2010 08:00:00 PM 20

2 12/10/2010 08:00:00 AM 50

2 12/10/2010 08:00:00 PM 40

1 12/11/2010 08:00:00 AM 20

1 12/11/2010 08:00:00 PM 10

2 12/11/2010 08:00:00 AM 40

2 12/11/2010 08:00:00 PM 60

Now I want to display a straight table that shows avg(value) per id per day

Result:

ID Date Value

1 12/10/2010 15

2 12/10/2010 45

1 12/11/2010 15

2 12/11/2010 50

Does any one know how to do this in qlikview (in the expressions/ in the script...anything is fine)?

Thanks in advance

averaging values using multiple aggregation fields

Thu, 16 Dec 2010 21:15:37 GMT

You can do it completely in the application (no script work). Start with ID as a dimension and then create another dimension: Date([Date-Time]). Then create an expression: Avg(Value).

EDIT: I ended up using Date([Date-Time]) in the load and then using Date#([Date-Time]) as the calculated dimension. Left([Date-Time], 10) may be easiest, since it works in the calculated dimension with no edits in the script.

averaging values using multiple aggregation fields

Fri, 17 Dec 2010 20:13:19 GMT

It worked. Thanks so much!