Set status if entries are available more than 5 percent

[ShubhamD_QS]

Hi All, 
Refer below mentioned data..take an example of only MyID=2 entries.

For MyID= 2, if Status =0 is appearing more than 5% then I want Expected Status column as a output Not Effective else NA

Numerator = Count of MyID if Status = 0 for MyID=2
Denominator = Count (MyID)

I want to set the Expected Status as a Not Effective, when repeated more than 5% for MyID=2
But not getting expected output. 
I am trying below expression but no luck.

Expected Status= 
Aggr(
    If(
        Status = 0,
        If(
            ((Count(If(Status = 0, Status )) / Count(Status )) * 100) > 5, 
            'Number 0'
        ),
        If(
        Status  = 1,
         If(
            ((Count(If(Status = 1, Status )) /  Count(Status ))* 100) > 5, 
            'Number 1'
        ),
         If(
         Status  = 2,
            ((Count(If(Status = 2, Status )) /  Count(Status ))* 100) > 5, 
            'Number 2'
        )))
,
    MyID
)

ID	MyID	Status	Expected Status
A1	1	1	NA
A2	1	2	NA
A3	1	0	NA
B1	2	0	Number 0
B2	2	0	Number 0
B3	2	0	Number 0
B4	2	0	Number 0
B5	2	1	NA
B6	2	1	NA

[clarachac]

Hello,

You can try with that expression : 

If(
MyID = 2 and
(Count({<Status={0}>} Status) / Count(Status)) * 100 > 5,
'Not Effective',
'NA'
)

[Kushal_Chawda]

@ShubhamD_QS  try below expression

=if(aggr(nodistinct count({<Status={0}>}Status),MyID,Status)/count(total <MyID>Status)>0.5,'Not effective','NA')

[ShubhamD_QS]

Hi @Kushal_Chawda  
Thanks for the revert.

I guess, upto certain extent it is working but problem is I need to add this column as a dimension not measure. 
Can we do something for that ?

[Kushal_Chawda]

@ShubhamD_QS  use this below calculated dimension

=aggr(if(aggr(nodistinct count({<Status={0}>}Status),MyID,Status)/count(total <MyID>Status)>0.5,'Not effective','NA'),MyID,Status)

[ShubhamD_QS]

Hi @Kushal_Chawda 

It is working as Measure when I tried and might work as a dimension as you have mentioned. 

But it work only when I select specific MyID. I guess, because of total <MyID> in denominator. 

When I don't select MyID, it takes count of all MyID and does calculation accordingly. I want this calculation to work for each MyID separately.

Below is your expression I am using for my requirement, which works only when I select that specific MyID. 

=if(
Status = 0,
if(aggr( nodistinct count({<Status={0}>}Status), MyID, Status)
/
count(total <MyID> Status) > 0.05, 'Not effective' , 'NA0'),

if(
Status = 1,
if(aggr( nodistinct count({<Status={1}>}Status), MyID, Status)
/
count(total <MyID> Status) > 0.05, 'Not Assessed' , 'NA1'),

if(
Status = 2,
if(aggr(nodistinct count({<Status={2}>}Status), MyID, Status)
/
count(total <MyID> Status) > 0.05, 'Effective' , 'NA2')
)))

Thanks once again.

[Kushal_Chawda]

@ShubhamD_QS  It's working for me

Data:
Load * Inline [
ID MyID Status
A1 1 1
A2 1 2
A3 1 0
B1 2 0
B2 2 0
B3 2 0
B4 2 0
B5 2 1
B6 2 1
B1 3 0
B2 3 0
B3 3 0
B4 3 1](delimiter is '\t');

Below is the calculated dimension

=aggr(if(aggr(nodistinct count({<Status={0}>}Status),MyID,Status)/count(total <MyID>Status)>0.5,'Not effective','NA'),MyID,Status)