17 Replies Latest reply: May 17, 2018 9:09 AM by Jordy Wegman

# Argh, rubbish at set analysis!

I am trying to calculatte Count(distinct [Attendance ID]) where WeekStart(Today() = WeekStart(Linkdate)

Give me a clue please?

Andy

• ###### Re: Argh, rubbish at set analysis!

May be this

Count(DISTINCT {<Linkdate = {"=WeekStart(Linkdate) = WeekStart(Today())"}>} [Attendance ID])

• ###### Re: Argh, rubbish at set analysis!

or

Count(DISTINCT {<Linkdate = {"\$(='>=' & WeekStart(Today()) & '<' & WeekStart(Today(), 1))"}>} [Attendance ID])

• ###### Re: Argh, rubbish at set analysis!

Sorry should have said I need the total for the current week

• ###### Re: Argh, rubbish at set analysis!

What does the above give you?

• ###### Re: Argh, rubbish at set analysis!

Unfortunately the same as Count(distinct [Attendance ID])

• ###### Re: Argh, rubbish at set analysis!

What is the Linkdate field format?

• ###### Re: Argh, rubbish at set analysis!

Hi sonny,

it was  Date(Floor(fk_date),'YYYY-MM-DD')AS LinkDate,

then changed it to

But didn't make any difference ;-)

• ###### Re: Argh, rubbish at set analysis!

Try this

Count(DISTINCT {<Linkdate = {"\$(='>=' & Date(WeekStart(Today()), 'YYYY/MM/DD') & '<' & Date(WeekStart(Today(), 1), 'YYYY/MM/DD'))"}>} [Attendance ID])

With this

• ###### Re: Argh, rubbish at set analysis!

You try: Count(Distinct {\$< Linkdate = {"=WeekStart(LinkDate) = WeekStart(Today())"}>} [Attendance ID])

• ###### Re: Argh, rubbish at set analysis!

Sorry still no joy

• ###### Re: Argh, rubbish at set analysis!

A sample to look at?

• ###### Re: Argh, rubbish at set analysis!

Sorry no can do!

• ###### Re: Argh, rubbish at set analysis!

okay

• ###### Re: Argh, rubbish at set analysis!

Hi Andy maybe is easier as:

Count(DISTINCT {<Linkdate = {">=WeekStart(Today())<=WeekEnd(Today())"}>} [Attendance ID])

To be in the same weekstart both dates have to be in the same today-week

• ###### Re: Argh, rubbish at set analysis!

Ruben - Did you miss the dollar sign expansions?

• ###### Re: Argh, rubbish at set analysis!

Sure, thanks:

Count(DISTINCT {<Linkdate = {">=\$(=Date(WeekStart(Today())))<=\$(=Date(WeekEnd(Today())))"}>} [Attendance ID])

Also date formats can be an issue here...Maybe I'm repeating something, I didn't read all the posts carefully.

• ###### Re: Argh, rubbish at set analysis!

I tried to propose these options (at least similar)... but nothing worked... but andy is not willing or able to share a sample... makes it difficult to say much...