Calculation of a count of clients for each first day of a week

[Anonymous]

Hello!

Target:

I need to calculate a count of clients for each first day of a week (count at the beginning of a period).

Example:

I've got a Straight table like this:

Week	Count( Distinct ClientID)	Count( DISTINCT  {<ClientID = P({1<Date={"$(=Date(WeekStart(min(Date))))"}>}ClientID)>}  ClientID )
23.01-2017 - 29-01-2017	34	0
02-01-2017 - 08-01-2017	23	3

My expression does not work correctly. Its calculates a result only for the last ("min") week.

How would look like a correct expression? (and what's wrong with my expression?)

Best wishes,

Evgeniy

[sunny_talwar]

Does a client need to be counted only once or a single client be part of multiple weeks? Also, would you be able to share some raw data to show what you have?

[Anonymous]

Thanks for the quick response, Sunny Talwar!

A single client can be a part of multiple weeks.

Two weeks' data for this example  is attached.

Probably, the right answer has to be like: 8 and 3.

Best wishes,

Evgeniy

[sunny_talwar]

I am getting 6 and 3, are you sure that 23.01.2017-29.01.2017 is 8?

[Anonymous]

Yes, you are right and 6 is correct. I can't go though your example right now, but I'll do it a bit later.

As I can see you've made some changes into load script.

is it possible to calculate this just using the 'Set Analysis'? (And what was wrong with my expression? )

Best wishes,

Evgeniy

[sunny_talwar]

Set analysis won't be possible, but I can get the result using Aggr function

[sunny_talwar · Accepted solution]

Actually, may be without Aggr()... you can try this

=Count(DISTINCT If(Date = Date#(SubField(Week, '-', 1), 'DD.MM.YYYY'), ClientID))

[Anonymous]

I'll check both variants, but I'm sure they will be correct!

Thank you so much for your help and attention!

Have a nice evening!

Best wishes,

Evgeniy

[Anonymous]

Hello!

I'd also like to know how to calculate customers who started purchasing (who made his first purchase) during each "week" like an expression without any influence in the load script.

Example of result:

Week	Count of New Clients
26.12.1016 - 01.01.2017	- who made his first purchase between 26.12.1016 - 01.01.2017
02.02.2017 - 08.01.2017	- who made his first purchase between 02.02.2017 - 08.01.2017 etc

Best wishes,
Evgeniy

[sunny_talwar]

This expression is based on no modifications made in the script... is this not working for you?

=Count(DISTINCT If(Date = Date#(SubField(Week, '-', 1), 'DD.MM.YYYY'), ClientID))