Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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).
May be this
NetWorkDays(
If(MonthName(StartWorkingDate) = MonthName(Min(acc_TradeDate)) and StartWorkingDate <= Min(acc_TradeDate), StartWorkingDate, Min(acc_TradeDate)),
Max(acc_TradeDate))
May be you need this
Count(Total <StartWorkingDate> {<AEID= ,acc_TradeDate = {">=$(=Date(Max( StartWorkingDate),'YYYY-MM-DD'))"}>} Distinct acc_TradeDate)
Total gives you Total of that expression....
Use without Total if you want to do individual counts
Thank you. But no luck Sunny.
Because each AEID does not have full acc_TradeDate, so i have to use Total to get a set of all acc_TradeDate, then i want to filter that set with each AEID's StartWorkingDate.
The expression you suggested returns only numbers of acc_TradeDate that "link" with each AEID.
Thanks Aehman.
I want to use the total outside and individual set inside. Is it possible in Qlik Sense?
Would you be able to share a sample to show what exactly you are doing?
I added more information. Please take a look at edited post.
Well, if Sunny is helping you out then I guess he is the best to get answer from.
I'm a learner as well and Set expressions always confuses me.
Why
AE_1 should be 17 ?
Why is there no data for AE_1 between 2017-10-09 and 2017-10-18?