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

[Anonymous]

This expression works correct. Thank you again!

It was the expression to calculate clients only for the first day of week, but now I have another one question which is based on the same data.

The question is I need to calculate a count of clients who has made his first purchase in the interval (from the first day of a week to the end day of a week). It will be "New Clients".

Something like that:

Count(DISTINCT aggr(if(Min(Date) <=  Date(WeekEnd(min(Date))), ClientID), ClientID) )  P.s. it doesn't work

Best wishes,

Evgeniy

[sunny_talwar]

May be this

New Client

=Sum(Aggr(If(Date = Min(TOTAL <ClientID> Date), 1, 0), ClientID, Date, Week))

Repeat Client

=Count(DISTINCT ClientID) - Sum(Aggr(If(Date = Min(TOTAL <ClientID> Date), 1, 0), ClientID, Date, Week))

[Anonymous]

Awesome! Thanks a million!

Best wishes,

Evgeniy

[Anonymous]

Hello, Sunny Talwar! I've got a small question about this expression.

New Client

=Sum(Aggr(If(Date = Min(TOTAL <ClientID> Date), 1, 0), ClientID, Date, Week))

It works correctly if I won't select, for example, a week.  Is it possible to rewrite this expression and optimize to a possibility to get right results based on some selection?

Best wishes,

Evgeniy

[sunny_talwar]

Can you share an example where this isn't working based on selection?

[Anonymous]

Thanks for the quick response!

An example is attached ( based on the xls file sent previously)

I select the second week and it's recalculated a value for the selected week. I'll appreciate if you give a new expression/explanation to me.

Best wishes,

Evgeniy

[sunny_talwar]

Try this

= sum( aggr(

if(Only({<Week>}Date) = min( TOTAL <ClientID> {<Week>} Date ) , 1 , 0 )

, ClientID, Date,Week ))

[Anonymous]

It's helped! Thank you very much and a Happy New Year!

Best wishes,

Evgeniy

[sunny_talwar]

Happy New Year my friend