?

[xyz1]

.

[avinashelite]

Could you share the sample data set for this ? how is the data jan , feb are all coming from different columns are in the same columns ..share the sample data will help you

[xyz1]

.

[avinashelite]

Find the attachment for the solution

[xyz1]

.

[avinashelite]

Temp:

LOAD Employee,

     Jan,

     Feb,

     Mar,

     Apr,

     May,

     Jun,

     Jul,

     Aug,

     Sep,

     Oct,

     Nov,

     Dec

FROM

\\Msad\root\AP\APBO\MI\users\ravinash\Downloads\Sample.xlsx

(ooxml, embedded labels, table is Sheet1);

CrossTable(Month,value)

LOAD Employee,

     Jan,

     Feb,

     Mar,

     Apr,

     May,

     Jun,

     Jul,

     Aug,

     Sep,

     Oct,

     Nov,

     Dec

FROM

\\Msad\root\AP\APBO\MI\users\ravinash\Downloads\Sample.xlsx

(ooxml, embedded labels, table is Sheet1);

Expression :

if(count({<value={0}>}value)=12,'Allocated',if(count({<value={"<0"}>}value)>=1,'Over Allocated',

if(count({<value={">0"}>}value)>1,'Avilable')

))

[effinty2112]

Hi Agrim,

You say:

>Count “Employee” who have all values less than ‘0’ from Jan-Dec as ‘Over allocated’

but no employees have all values less than zero, did you mean

>Count “Employee” who have some values less than ‘0’ from Jan-Dec as ‘Over allocated’

?

Regards

Andrew

[ahaahaaha · Accepted solution]

Hi,

May be like this?

Table1:

LOAD Employee,

    Jan,

    Feb,

    Mar,

    Apr,

    May,

    Jun,

    Jul,

    Aug,

    Sep,

    Oct,

    Nov,

    Dec

FROM

[https://community.qlik.com/thread/269289?sr=inbox&ru=232295]

(html, codepage is 1251, embedded labels, table is @1);

Table2:

CrossTable(Month, Value)

LOAD*

Resident Table1;

//check the first condition

Table3:

LOAD

Employee,

If(Count(Value)=0, 'Allocated') as Allocated

Resident Table2

Where Value<>0

Group By Employee;

//check the second condition

Table4:

LOAD

Employee,

If(Count(Value)>0, 'Over Allocated') as OverAllocated

Resident Table2

Where Value<0

Group By Employee;

//check the third condition

Table5:

LOAD

Employee,

If(Count(Value)=12, 'Available') as Available

Resident Table2

Where Value>=0

Group By Employee;

//attached the results to the main table

Left Join (Table1)

LOAD*

Resident Table3;

Left Join (Table1)

LOAD*

Resident Table4;

Left Join (Table1)

LOAD*

Resident Table5;

DROP Tables Table2, Table3, Table4, Table5;

Result

Example at attached file.

Regards,

Andrey

[effinty2112]

Hi Agrim,

Try:

Data:

CrossTable(Month,Value)

LOAD * INLINE [

    Employee, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec

    Adeline HEUSELE, 22, 21, 10, 17, 18, 19, -1, -2, 23, 23, 21, 22

    Alexandre DHELLEMME, 23, 22, -1, 1, 1, 2, 0, -1, 23, 24, 20, 21

    Alexandre FAVRE, 22, 22, 19, 16, 14, 16, -4, -5, 18, 20, 18, 16

    Amandine SCOUMAQUE, 21, 21, 23, 21, 20, 22, -1, 0, 23, 24, 17, 12

    Amarish THAKAR, 22, 20, 22, 18, 9, 10, 10, 8, 9, 7, 12, 4

    Anthony LEMAI, 21, 21, -1, 5, 6, 12, 12, 11, 14, 15, 22, 23

    Antoine GOUVERNEUR, 21, 21, 24, 22, 19, 21, 0, 2, 23, 23, 21, 20

];

then

Count Allocated =count(DISTINCT {$<Employee = E({<Value -= {0}>})>}Employee)

Count Over Allocated =count(DISTINCT {$<Value = {"<0"}>} Employee)

Count Available =count(DISTINCT {$<Employee = E({<Value = {"<0"}>})>} Employee)

Allocated Employees =concat(DISTINCT {$<Employee = E({<Value -= {0}>})>}Employee,', ')

Over Allocated Employees  =concat(DISTINCT {$<Value = {"<0"}>} Employee,', ')

Available Employees  =concat(DISTINCT {$<Employee = E({<Value = {"<0"}>})>} Employee,', ')

Regards

Andrew

[xyz1]

.