Challange with pivot table

[#1]

Hi all,

I've some challange with get the information I want in a pivot table.

First I've som dimensions:

- CC

- EmpGroup

- TimeType

- EmpID

Then I've a expression:

if(IsNull(STARTTime)= -1, Absence, Timestamp(STARTTime,'hh:mm')) so I get STARTTime and if there are no STARTTime I will have the Absence.

This doesen't work that well so I change the Expression like this:

sum(aggr(if(IsNull(STARTTime)= -1, Absence, Timestamp(STARTTime,'hh:mm')), STARTTime, Absence, CC,EmpGroup,TimeType,  EmpID))

This works much better so I get all the STARTTime but I will not get any Absence.

Any one that have a suggestion?

[Gysbert_Wassenaar]

Something like count(Alt(STARTTime, Absence)). Though I have no idea what you're trying to calculate.

[Qrishna]

Try

if(len(STARTTime)= 0, Absence, Timestamp(STARTTime,'hh:mm'))

this returns Timestamp(STARTTime,'hh:mm') when len(STARTTime) is not zero else 'Absence'

[swuehl]

For every combination of dimension values, is it guaranteed that there is only one unique value of STARTTime or Absence? I assume it's not, so you need to consider using

a) aggregation functions!

Use Aggregation Functions!

b) add dimensions, so that it's guaranteed that there is only() unique value per combination of dimensional values.

You are potentially summing different STARTTimes in your second expression, is this really what you want (summing times? or do you want to sum intervals? Or do  you just show a single value?)

[#5]

Yes it should be only one unique single value of STARTTime or Absence.

I just want to show a single value, if there are any STARTTime show that otherwise Absence.

[#6]

It works like my fortst expression: if(IsNull(STARTTime)= -1, Absence, Timestamp(STARTTime,'hh:mm'))

So unfortunately it don't give me the right values.

[#7]

I just want to show value, if there are any STARTTime show that otherwise Absence.

The attached picture does only show STARTTime.

[swuehl]

What if you expand all dimensions? I think with collapsed dimensions, you probably don't have unique values.