topic expression -- help needed in QlikView

expression -- help needed

Anonymous — Tue, 21 Sep 2010 13:15:47 GMT

I have a table containing data which somehow looks like this:

ServiceId	Before	After	DateTime	UpgradeConf	....
1	456	234	9-9-2010 11:45:13	C2.1
1	234	654	10-9-2010 13:43:34	C3.1
2	344	132	9-9-2010 11:45:54	C2
2	132	564	10-9-2010 11:49:12	C2i
2	344	654	11-9-2010 12:41:13	C3.1
3	112	234	13-9-2010 13:44:32	C2i

What I want to achieve is something like this:

UpgradeConf	Amount	...
C2	0
C2i	1
C2.1	0
C3.1	2

The amount for the UpgradeConf should be the sum of the last known value for the service Id ... depending on the selected date ...

So If I have a date selected like 10-9-2010 the following should be shown:

UpgradeConf	Amount	...
C2	0
C2i	1
C2.1	0
C3.1	1

I don't have a clue how to solve this ... anyone got suggestions??

thx in advance,

Anita

expression -- help needed

Miguel_Angel_Baeyens — Tue, 21 Sep 2010 13:53:17 GMT

Hello Anita,

You may use ConfUpgrade as dimension and the following as expression

aggr(Count(ServiceId), UpgradeConf, DateTime)

Hope that helps

expression -- help needed

Anonymous — Tue, 21 Sep 2010 14:05:46 GMT

This is not totally the solution i guesse ...

DateTime should not be exactly the date time ...

Lets say the serviceId 1 has the following date/times:

9-10-2010 12:34:56
9-10-2010 16:14:44
11-10-2010 23:10:21
12-10-2010 14:11:35
15-10-2010 21:53:25

When the selected Date=

9-10-2010 -- the 9-10-2010 16:14:44 should be taken
10-10-2010 -- the 9-10-2010 16:14:44 should be taken
11-10-2010 -- the 11-10-2010 23:10:21 should be taken
12-10-2010 -- the 12-10-2010 14:11:35 should be taken
15-10-2010 -- the 15-10-2010 21:53:25 should be taken

So the date selected has effect on the selection.

The last row for each ServiceId which is lower or equal to the selected date should be taken into account ...

rgrds