topic Re: Calculation of a count of clients for each first day of a week in QlikView

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

Anonymous — Mon, 18 Dec 2017 14:18:42 GMT

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

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

sunny_talwar — Mon, 18 Dec 2017 14:22:41 GMT

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?

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

Anonymous — Mon, 18 Dec 2017 14:33:28 GMT

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

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

sunny_talwar — Mon, 18 Dec 2017 14:43:14 GMT

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

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

Anonymous — Mon, 18 Dec 2017 15:02:20 GMT

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

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

sunny_talwar — Mon, 18 Dec 2017 15:05:27 GMT

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

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

sunny_talwar — Mon, 18 Dec 2017 15:06:38 GMT

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

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

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

Anonymous — Mon, 18 Dec 2017 15:13:49 GMT

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

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

Anonymous — Tue, 26 Dec 2017 10:04:32 GMT

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

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

sunny_talwar — Tue, 26 Dec 2017 12:09:54 GMT

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

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

Anonymous — Tue, 26 Dec 2017 12:29:47 GMT

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

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

sunny_talwar — Tue, 26 Dec 2017 13:22:54 GMT

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

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

Anonymous — Tue, 26 Dec 2017 13:35:01 GMT

Awesome! Thanks a million!

Best wishes,

Evgeniy

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

Anonymous — Thu, 04 Jan 2018 08:59:07 GMT

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

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

sunny_talwar — Thu, 04 Jan 2018 09:23:07 GMT

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

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

Anonymous — Thu, 04 Jan 2018 09:52:06 GMT

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

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

sunny_talwar — Thu, 04 Jan 2018 09:56:36 GMT

Try this

= sum( aggr(

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

, ClientID, Date,Week ))

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

Anonymous — Thu, 04 Jan 2018 11:37:38 GMT

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

Best wishes,

Evgeniy

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

sunny_talwar — Thu, 04 Jan 2018 12:00:43 GMT

Happy New Year my friend