7 Replies Latest reply: Oct 20, 2017 1:27 AM by Shamit Shah

# 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

• ###### Re: Set analysis to find distict values

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

• ###### Re: Set analysis to find distict values

Hi Anil,

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

Thanks

• ###### Re: Set analysis to find distict values

Not sure, Would you able to provide sample

• ###### Re: Set analysis to find distict values

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

• ###### Re: Set analysis to find distict values

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

let vTodayDate = Date(Today());

And expression is like

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

• ###### Re: Set analysis to find distict values

Hi,

Can you try like this?

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

Thanks,

Deva

• ###### Re: Set analysis to find distict values

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