Pivot Table / Chart - How to aggregate

[#1]

Hello,

sorry for this stupid question, but I just started with QlikView, but can not resolve this issue by myself. I am familiar how Pivot tables work from my experience with the Excel Assistant / Layout.

Now I would like to do the same with Qlik butcan not reach the same result.

I would like to get a table &chart where all values for one day averaged in one number/bar. With the Pivot table as it stands (in my case) I get as many bars/day as I have lines per day - no aggregation over one day takes place.

How to configure Qlik Pivot table to make this work? In Excel it is so easy and obvious ... ;-).

Here is my table:

Date/Timestamp Identifier value

---------------------------------------------------

05.10.09 1 123456

05.10.09 2 6293737

06.10.09 3 42637

06.10.09 4 182829

06.10.09 5 273738

......

Thank you

Kind regards

Matthias

05.10.2009	16473	294404
05.10.2009	16474	221970
05.10.2009	16475	772188
05.10.2009	16477	900615
05.10.2009	16476	6044
05.10.2009	16487	534191
05.10.2009	16486	249359
05.10.2009	16545	655765
05.10.2009	16546	13600
05.10.2009	16592	628629
06.10.2009	16584	583798
06.10.2009	16596	69769
06.10.2009	16631	365732
06.10.2009	16634	782447
07.10.2009	16718	655914
07.10.2009	16750	73824
07.10.2009	16726	1246742
07.10.2009	16746	451110
07.10.2009	16747	6957
07.10.2009	16748	385539
08.10.2009	16850	14209
08.10.2009	16899	621370
08.10.2009	16911	90477
09.10.2009	17015	53197
09.10.2009	17027	820366
09.10.2009	17050	391570
12.10.2009	17263	482657
12.10.2009	17270	25985
12.10.2009	17271	381098
12.10.2009	17277	617390
12.10.2009	17293	770595
12.10.2009	17315	657016
12.10.2009	17317	1039616
12.10.2009	17319	749370
13.10.2009	17390	467258
13.10.2009	17395	483056
14.10.2009	17451	528672
14.10.2009	17465	580580
14.10.2009	17469	929472
14.10.2009	17505	768573
14.10.2009	17523	1841
15.10.2009	17549	296553
15.10.2009	17550	146152
15.10.2009	17554	645128
15.10.2009	17556	890517
15.10.2009	17568	171570
15.10.2009	17569	415791
15.10.2009	17570	207721
15.10.2009	17571	136995
15.10.2009	17573	323188
16.10.2009	17587	808192
16.10.2009	17602	677691
16.10.2009	17627	769774
16.10.2009	17629	585635
16.10.2009	17648	8743
16.10.2009	17650	1079960
16.10.2009	17653	385796
17.10.2009	17662	203021
17.10.2009	17663	11685
17.10.2009	17668	62828
17.10.2009	17670	339417
17.10.2009	17674	498321
18.10.2009	17688	3643
18.10.2009	17689	36165
18.10.2009	17697	94749
19.10.2009	17707	781213
19.10.2009	17709	792304
19.10.2009	17710	1046295
19.10.2009	17711	707525
19.10.2009	17714	10874
19.10.2009	17724	489382
19.10.2009	17738	60178
19.10.2009	17739	262085
19.10.2009	17740	197055
20.10.2009	17760	102693
20.10.2009	17770	445030
20.10.2009	17779	339694
20.10.2009	17780	346970
20.10.2009	17781	759080
20.10.2009	17784	346832
20.10.2009	17783	938084
20.10.2009	17786	913584
20.10.2009	17785	995135
20.10.2009	17790	12562
20.10.2009	17795	775687
20.10.2009	17797	114903
20.10.2009	17798	133445
21.10.2009	17811	1085333
21.10.2009	17813	1149254
21.10.2009	17819	777604
21.10.2009	17823	196839
21.10.2009	17834	586000
21.10.2009	17840	1361039
21.10.2009	17841	779321
21.10.2009	17844	350578
21.10.2009	17850	536449
22.10.2009	17861	578421
22.10.2009	17862	5197
22.10.2009	17865	856606
22.10.2009	17872	711357
22.10.2009	17880	558265
22.10.2009	17881	894809
22.10.2009	17886	166856
22.10.2009	17894	947720
22.10.2009	17898	354579
22.10.2009	17899	713327
23.10.2009	17918	778394
23.10.2009	17932	878634
23.10.2009	17935	643235
23.10.2009	17941	598476

[johnw · Accepted solution]

You want to use Date as the dimension, and avg(Value) as the expression.

However, when you say your table has a Date/Timestamp, is it a date or a timestamp? If it's a timestamp, then that would cause you to get one row per record. You need a date field, which you can extract from a timestamp by using the floor() function, either to make a new field, or in a calculated dimension.

[#3]

Hello John,

thanks for your hint - works just great now. I just started with Qlikview and my sw engineering skills became a bit rusty, but I need to catch up quickly.

Thank you.

P.S. In case you are a freelancer and interested in a project then ping me with your contact details.

Kind regards

Matthias

[johnw]

Sorry, happily employed. Glad it works now, though.