Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Challange with pivot table

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,TimeTypeEmpID))

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


Any one that have a suggestion?

7 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Qrishna
Master
Master

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
MVP
MVP

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?)

Not applicable
Author

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.

Not applicable
Author

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.

Not applicable
Author

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

The attached picture does only show STARTTime.


Time_Absence.JPG

swuehl
MVP
MVP

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