basic crosstable load and summation of colums

[#1]

Hi,

I have a sample source table :

to explain the table on the first row:

LF3 is a machine line, working for 140 minutes, which of 30 minutes were downtime (C1 to C5 are Downtime types)

So you would like to make a Crosstable : CrossTable(DowntimeType, Downtime, 2)

This would give :

Line	Total	DowntimeType	Downtime
LF3	140	C1	10
LF3	140	C2	0
LF3	140	C3	0
LF3	140	C4	0
LF3	140	C5	20
DF1	330	...

Now I would like to make calculations on it :

LF3 = 140 - 30

so I thought : =sum(Total) - sum(DownTime)

but for LF3 I get 700 - 30 = 630

I could do avg(Total) - sum(DownTime)

But I'm afraid I won't get away with that when I have multiple LF3 value entries for different dates

Can someone help me?

Thank you

[Anonymous · Accepted solution]

There are several ways to solve this.

If you want to work with to sum of all your downtime types I would suggest to sum them in your loading script ,like:

Load      Line,

          Total,

          C1,

          C2,

          C3,

          C4,

          C5,

          C1+C2+C3+C4+C5 as TotalDownTime

From ...

This creates an extra field which you can use and you are still able to use the different types as well

Let me know if this works for you, ok?

Good luck,

Dennis.

[#3]

He Dennis,

C1+C2+C3+C4+C5 as TotalDownTime doesn't work when I want to sum more than 2 fields at the same time.