Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shamitshah
Partner - Creator
Partner - Creator

Set analysis to find distict values

Hi,

I have the following expression which gives me the total count of the number of clients over a fortnight period (current week + previous week):

count(distinct{<[Shifts.CreatedStartDate] = {">=$(=Date(WeekStart(Today()),'DD/MM/YYYY'))"}>}[Client])

+count(distinct{<[Shifts.FULLPRW]={1}> }[Client])

Now I need to find the number of distinct clients over the fortnight. Any ideas on the expression?

Thanks

7 Replies
Anil_Babu_Samineni

May be this?

Sum(DISTINCT Aggr(count(distinct{<[Shifts.CreatedStartDate] = {">=$(=Date(WeekStart(Today()),'DD/MM/YYYY'))"}>}[Client]) +count(distinct{<[Shifts.FULLPRW]={1}> }[Client]), Dim1, Dim2))

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
shamitshah
Partner - Creator
Partner - Creator
Author

Hi Anil,

Tried the above. It's not giving the right result.

Thanks

Anil_Babu_Samineni

Not sure, Would you able to provide sample

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
shamitshah
Partner - Creator
Partner - Creator
Author

I don't have a sample as such.

Just looking at the data again, I can see that fortnight periods have been defined and values e.g shifts created for each fortnight.:

Fortnight                       No

3/11/2017                      90

8/10/2017                    100

24/09/2017                   95

10/09/2017                   97

What would the set expression be to get the numbers for the latest fortnight ?    There are future fortnight periods in the data which I want exclude. e.g.3/11/2017. So I am trying to get an expression which will show me the figures for the latest fortnight only i.e. 8/10/2017.

Thanks

Anil_Babu_Samineni

This case, My suggestion always go with variable, Like?

let vTodayDate = Date(Today());

And expression is like

Sum({<Fortnight = {"<=$(vTodayDate)"}>} No)

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
devarasu07
Master II
Master II

Hi,

Can you try like this?

=SUM({<Fortnight ={">=$(=MonthStart(Today())) <=$(=MonthEnd(Today()))"}>} No)

Capture.JPG

Thanks,

Deva

shamitshah
Partner - Creator
Partner - Creator
Author

Hi Devarasu,

I tried your expression  it works. What happens when your fortnight period is in a different month than today? I think your expression only picks the dates in the relevant month. Correct me if I am wrong.

Thanks