Set analysis to find distict values

[shamitshah]

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

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

[shamitshah]

Hi Anil,

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

Thanks

[Anil_Babu_Samineni]

Not sure, Would you able to provide sample

[shamitshah]

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)

[devarasu07]

Hi,

Can you try like this?

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

Thanks,

Deva

[shamitshah]

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