Expression within set analysis

[#1]

Hello,

I'm new  to the forum. I wonder if anybody can help me....

Essentially, in pseudo code I'm looking to create an expression that can does the below:

min (

               if (

                    Count({<[Location] = {'At work'}>} People) < Count(({<[Location] = {'Not at work'}>} People), Time, Nothing

                    )

     )

so basically, in my data, there will be many times across the day when the number of people 'Not at work' is greater than those 'At work', but I want to return the minimum time this happens.

This expression will be put in a straight table chart where the Dimension is DayofWeek. So essentially I want to find out the minimum time when 'Not at work' > 'At work' per day.....

any thoughts or suggestions would be much appreciated!

Colin

[robert_mika]

Could you post sample of your data?

[#3]

Unfortunately I cant post the data in its current format, but can work on creating a sample doc to share.

An abbreviated data structure could be shown as:

People     Timeslot          Day          Location

A               1                    1               At Work

A               2                    1               At Work

A               3                    1               Not At Work

A               4                    1               At Work

B               1                    1               Not At Work

B               2                    1               Not At Work

B               3                    1               Not At Work

B               4                    1               Not At Work

C               1                    1               At Work

C               2                    1               At Work    

C               3                    1               Not At Work

C               4                    1               Not at Work

D               1                    1               At Work              

D               2                    1               At Work

D               3                    1               Not At Work    

D               4                    1               Not At Work

So for Day 1,

Timeslot 1 has 3 people at work and 1 not.

Timeslot 2 has 3 people at work and 1 not

Timeslot 3 has 4 people not at work.

Timeslot 4 has 4 people not at work

I would want the expression to output '3' for Day1 etc as its the minimum timeslot where count({Not At Work} People) > Count({At Work} People)

Hope that helps?

[Eugeny_Ilyin]

Hi,

script example:

tmp1:
 LOAD * INLINE [
People, Timeslot, Day, Location
A,1,1,At Work
A,2,1,At Work
A,3,1,Not At Work
A,4,1,At Work
B,1,1,Not At Work
B,2,1,Not At Work
B,3,1,Not At Work
B,4,1,Not At Work
C,1,1,At Work
C,2,1,At Work    
C,3,1,Not At Work
C,4,1,Not at Work
D,1,1,At Work              
D,2,1,At Work
D,3,1,Not At Work    
D,4,1,Not At Work              
];

temp1:
LOAD Distinct Day, Timeslot Resident tmp1;


temp2:
left join (temp1) load Day, Timeslot, count(People) as NAW
Resident tmp1 
where Location='Not At Work'
Group by Day,Timeslot ;

left join (temp1) load Day, Timeslot, count(People) as AW
Resident tmp1
where Location='At Work'
Group by Day,Timeslot ;

DROP Table tmp1;

temp3:
load Day, min(Timeslot) as MinTimeslot
Resident temp1
where (if(NAW>0,NAW,0)-if(AW>0,AW,0))>0
group by Day;

DROP Table temp1;