DATE_IN and DATE_OUT in Master Calender

[beck_bakytbek]

Hi Folks,

i got a Question, i am sure that you had such issue:

i have the following fields within my table:

DATE,               EMPLOYEE,        SALES,         DATE_IN,           DATE_OUT

01.01.2019,            Mike,             50,                   -                          -

03.04.2019,            Mike,             25,                  -                          -

01.01.2018,            Mike,             10,               01.01.2018,        12.12.2018

i build my Master Calender on the base of field: DATE, if i select the 2019 within of Master Calender, so i see the sum of Sales of Employee: MIKE = 85,

but you see that the Employee: MIKE has left the Company an 12.12.2018 (DATE_OUT)

i have here 2 Question:

1.Question: how can i compare the fields: DATE_IN and DATE_OUT with my field: DATE (with my Master Calender)

2. Question: if i select in my master calender the year: 2018 i should see only the sum of Mike = 10, and if i select the Year 2019, i should see only the sum 75 of employee: MIKE. (how can i compare the Fields: DATE_IN, DATE_OUT with my selection of Master Calender / or it is possible to solve this issue by using of set expression )

does anybody have any idea how to solve this issue?

Thanks a lot in advance for your help and time

[marcus_sommer]

If it's already in one table you could just flag it, maybe with something like this:

pick(rangesum(-(DATE >= DATE_IN and DATE <= DATE_OUT), 1), 'inactive', 'active') as EmployeeActiveFlag

- Marcus

[beck_bakytbek]

Hi Marcus,

thanks a lot for your help and feeback, do you mean, i can use this Expression in scriptarea? how can i implement it in comparison with my master calender? or am i missing something?

Thanks a lot

[marcus_sommer]

Yes, it's meant for the script. But I don't have any idea what do you mean with comparison ... ? The flag-value is related to the date field and with it it's connected to the master-calendar and it could display if sales were made from active or inactive people (in many enterprises it's not possibele because someone would inherit the sales of leaving employees) and may use it as dimension, listbox or set analysis condition.

- Marcus

[beck_bakytbek]

Hi Marcus,

with comparison i mean, if i select the year 2019, so i should see the Sales only from 2019, and not from 2018, because in 2018 the employee has left the company

[beck_bakytbek]

Hi Marcus,

thanks a lot for your help, the Expression your shared, does work great, but i want make this Expression as dynamic,

i mean if i choose the 2019, i see only the sum of 2019, but if i dont choose any selection in my master calender - filter: 2018, 2019 the i should see the whole sales (both years) dispite of DATE_IN and DATE_OUT,

i hope i explained well enough

thanks a lot

[marcus_sommer]

I must admit that I couldn't comprehend the problem. Your mentioned selection of the years will work like you describe - but it's completely unrelated to the IN and OUT - therefore the question what should be different?

- Marcus

[beck_bakytbek]

Hi Marcus,

i attached the screenshot, so my Point is:

if i select the Year 2018 i want to see only the active employee, but if i dont select anything, i want to see all employees (the EmployeeActiveFlag will be calculated on the base of comparison of fields: DATE_IN and DATE_OUT with Comparison of Field DATE)

in this example i see both employees (inactive and active), i want see only active employee (but if i select the year)

[marcus_sommer · Accepted solution]

Maybe with something like this:

sum({< EmployeeActiveFlag = {"$(=pick(ceil(getselectedcount(Year)/100)+1, '(active|inactive)', 'active'))"}>} Sales)

- Marcus

[beck_bakytbek]

Marcus,

thanks a lot for help and time 😃