
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Set analysis with total
Hello everyone,
I have an expression:
Count(Total {<AEID= ,acc_TradeDate = {">=$(=Date(Max( StartWorkingDate),'YYYY-MM-DD'))"}>} Distinct acc_TradeDate)
which used on a simple straight table with Dimension: AEID and StartWorkingDate.
This expression is used to count all the TradeDate from employee's start working date.
The problem is when i use the expression above, it shows the same value for all AEID with differebt StartWorkingDate.
Can you help me with this?
Here the model im working on:
Message was edited by: Jeremy Lee
I added SampleData for more detail. In the data we have 2 AEIDs. Assume that user is selecting TradeDate from 1 to 31-Oct to view the data in Oct 2017. (Date format is mm/dd/yyyy)
+ AE_2: this employee starts to work on 11/15/2016, so he has 22 acc_TradeDate for this month (known as working date in month)
+ AE_1: this emplyee starts to work on 10/09/2017 (this month), he started to have the first acc_TradeDate in 19/10/2017, so he just has 9 acc_TradeDate in the data.
But the business requirements is AE_2 should be counted 22 working date, and AE_1 should be 17 (count from the Start working date 10/09/2017).
- « Previous Replies
-
- 1
- 2
- Next Replies »

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
His start working day is 2017-10-09 but many days later (2017-10-18) he starts to manage some accounts. And business requirement need to count working day from the start working date.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
His start working day is 2017-10-09 but many days later (2017-10-18) he starts to manage some accounts. And business requirement need to count working day from the start working date.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How about this?
NetWorkDays(Only({<AEID}>} StartWorkingDate), Max({<AEID>} acc_TradeDate))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Seem you nearly made it Sunny. But we have to add below limit to the expression. I mean AE_2 should be 22 instead of 251. And the one more problem when selecting AE_1, the table shows all AE because the set expression disregarding AEID.
Detail in picture below:

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May be this
NetWorkDays(
If(MonthName(StartWorkingDate) = MonthName(Min(acc_TradeDate)) and StartWorkingDate <= Min(acc_TradeDate), StartWorkingDate, Min(acc_TradeDate)),
Max(acc_TradeDate))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Great Sunny. It works like a charm! You save my day. Thanks you so much for your strong support.

- « Previous Replies
-
- 1
- 2
- Next Replies »