How to get this

[alvinford]

Hi All,

Trying to get the following logic. I have the following details in table

i.e. Teacher ID, Teaching Code, Status , Start Date and End Date.

I want to exclude the Teacher ID's who are in Training (teaching Code A16,A17,A18)  when the Year or

Month of Teaching period  falls in between the Start Date and End Date is selected.

For Example : If Year 2013 and Month 4 is selected, distinct count(Teacher ID) should be 5. i.e. Teacher ID 50440 and 54240 to be excluded from the distinct count of Teacher ID as they are in the training.

Please find the attachment for your reference.

Regards,

Alvin.

[tamilarasu]

Hi Alvin,

Check the below expression

=Count(DISTINCT {<[Teaching Code]-={'A16','A17','A18'}>}[Teacher ID])

[#3]

Hi Alvin,

Checking your data, in year 2013 there are 5 distinct Teacher ID & for the month 4 there are 3 distinct teacher Id & if you remove the In training, then the answer would be 2.

Please check the below expression for the same

=count(DISTINCT(if(Status<>'In Training', [Teacher ID])))

Regards,

Anand B Nagaraj

[#4]

Hi alvinford,

Use Dimension Teacher Id and Teaching Code, and  expressions as

1)maxstring({<Status-={'In Training'}>}Status)

2)maxstring({<Status-={'In Training'}>}[Start Date])

3)maxstring({<Status-={'In Training'}>}[End Date])

Suppress Null value for both dimension values.

Please find attachment. This will help you.

Regards,

Devanand

[#5]

Hi,

Sorry, I just misunderstood your Requirement.

Just do :

1)maxstring({<Status-={'In Training', 'End of Training', 'Extension of Training'}>}Status)

2)maxstring({<Status-={'In Training', 'End of Training', 'Extension of Training'}>}[Start Date])

3)maxstring({<Status-={'In Training', 'End of Training', 'Extension of Training'}>}[End Date])

Neglect old attachment.

[Anonymous]

This part is missing in this expression:

when the Year or Month of Teaching period  falls in between the Start Date and End Date is selected.

Ford,

Could you share a sample app or let us know how you have derived your calender??

Is it from startdate & enddate?

[tamilarasu]

Not understood the requirement. Let's wait for sample application .

[jan_gunnars]

I think this will do it. It assumes that you will filter on year, month and/or date. See .zip.

I also guess that you mean 4 teachers 10776,10777,10778,10779

[alvinford]

Hi Nagaraj,

Thank You for Your reply.. Would like to achieve it from the Backend.

Regards,

Alvin.

[jan_gunnars]

Have another look and you will se it.