Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jeremylee
New Contributor II

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:

qlik.png

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).

1 Solution

Accepted Solutions

Re: Set analysis with total

May be this

NetWorkDays(

If(MonthName(StartWorkingDate) = MonthName(Min(acc_TradeDate)) and StartWorkingDate <= Min(acc_TradeDate), StartWorkingDate, Min(acc_TradeDate)),

Max(acc_TradeDate))

15 Replies

Re: Set analysis with total

May be you need this

Count(Total <StartWorkingDate> {<AEID= ,acc_TradeDate = {">=$(=Date(Max( StartWorkingDate),'YYYY-MM-DD'))"}>} Distinct acc_TradeDate)

Khan_Mohammed
Honored Contributor II

Re: Set analysis with total

Total gives you Total of that expression....

Use without Total if you want to do individual counts

jeremylee
New Contributor II

Re: Set analysis with total

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.

jeremylee
New Contributor II

Re: Set analysis with total

Thanks Aehman.

I want to use the total outside and individual set inside. Is it possible in Qlik Sense?

Re: Set analysis with total

Would you be able to share a sample to show what exactly you are doing?

jeremylee
New Contributor II

Re: Set analysis with total

I added more information. Please take a look at edited post.

Khan_Mohammed
Honored Contributor II

Re: Set analysis with total

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.

antoniotiman
Honored Contributor III

Re: Set analysis with total

Why

AE_1 should be 17 ?

Re: Set analysis with total

Why is there no data for AE_1 between 2017-10-09 and 2017-10-18?

Community Browser