How to display fixed datas set per month

[#1]

Hello,

I currently work on a table to view the evolution of a customer base over the months, however, I have a problem with an expression.


Usually I was working on orders, sales etc. ... and so I based my time axis on the "Payment Date".


To this array of clients I manually create a new independent date field containing the last day of each month:

date_generique:
LOAD * INLINE [
date_g
'2009-05-31 '
'2009-06-30 '
'2009-07-31 '
'2009-08-31 '
'2009-09-30 '
'2009-10-31 '
'2009-11-30 '
'2009-12-31 '
'2010-01-31 '
'2010-02-28 '
'2010-03-31 '
];

I then create a (graphical) Table which I simply assigned the following dimension:

= 'Base customers' & MonthName (date_g)


Here is my expression:

-> Total number of customers at the end of the month:

= Sum (Aggr (Max (if (date_client <= date_g, 1,0)), id_membre, date_g))

-> New Customers in that month:

= Sum (Aggr (Max (if (date_client> = monthsstart (1 date_g) AND date_client <= monthsend (1 date_g), 1,0)), id_membre, date_g))


Date_client which corresponds to the date of customer acquisition.

Here is an expression that I have a problem:
I would like to display the number of customers who made at least one purchase in the month! Here's what I started to write:

= Sum (Aggr (Max (if (date_paid_ymd = date_g, 1,0)), id_membre, date_g, date_paid_ymd))


However the figures shown are false.

Thank you in advance.

(Please tell me if there is a better way to organize my data to carry out this table)

[Anonymous]

You could consider not manually add last dates of the month ... There are functions to retrieve e.g. the lastday of the month ... MonthEnd() for the "Payment dates".

When loading the data you could add an extra field containing the MonthEnd() date on which you base your selection.

But if you are only interested in the month you could create a calendar for selecting the dates ...

[#3]

Thanks, you are right.

To be honest I already did it with executing this code on reload :

LET date_g_begin = Num('2007-12-01');

LET date_g_end = Num(Today(0));

for date=$(date_g_begin) to $(date_g_end)

date_generique:

LOAD DISTINCT monthsend(1,date($(date))) as date_g autogenerate(1);

next

And using a calendar could be a good solution too ... but my real problem is in the expression .. I'm working on it and I got it now :

= Sum( Aggr ( Max ( if(date_paid_ymd >= monthsstart(1,date_g) AND date_paid_ymd <= date_g,1,0) ) , id_membre, date_g, date_paid_ymd) )

I got more interesting results (I have to verify if they are correct), but my table is very slow to refresh .... (and I only try to show one month)

[Anonymous · Accepted solution]

What way is your calendar connected to the table you use?? If you have a connection with the dates it is not hard to create selections ... an no difficult expressions are necessary ...

If you select a month all items will be selected within that month ...

Lets say you have a calendar like below:

LET vMinDate = NUM(DATE('1-12-2007'));
LET vMaxDate = NUM(TODAY());

TempCalendar:
LOAD
 $(vMinDate)+ITERNO() AS NUM,
 DATE($(vMinDate)+ITERNO()-1) AS TempDate

AUTOGENERATE (1) WHILE
 $(vMinDate)+ITERNO()-1 <= $(vMaxDate);

Calendar:
LOAD
 TempDate AS "Payment Date",
 YEAR(TempDate) AS Year,
 APPLYMAP('Quarter', NUM(MONTH(TempDate))) AS Quarter,
 MONTH(TempDate) AS Month,
 WEEK(TempDate) AS Week,
 WEEKDAY(TempDate) AS WeekDay,
 DAY(TempDate) AS Day,
 DATE(MONTHSTART(TempDate), 'MMM-YYYY') AS MonthYear,
 MONTHEND(TempDate) as MonthEnd

RESIDENT
 TempCalendar
ORDER BY
 TempDate;

In this case the right data is already selected when you selected e.g. a month according to the Payment Date ... it makes the expression a lot simpler.

[#5]

Hey, thanks for this code,

Do you mean I have to replace "Payment Date" by my payement date field used in my datas ?

Because The problem is, that i want to use my "calendar date" tu compare with various dates, like "date_client" for my two first expressions in order to count Total client, and new clients each month ....

Any ideas ?

[Anonymous]

Yes the "Payment Date" mentioned in the calendar should be the same name as your field ... this way the calendar is connected to your table.

When you select a Month all payment dates in that month will be selected.

Snail wrote: "calendar date" tu compare with various dates, like "date_client"

You mean the same Month selection should be compared to another date?? Could compare this in an expression ofcourse ...

[#7]

OK,

I tried your solution, which is much better than mine, IF ( ) I can associate My number of customers and new customers in the same table !

Dimension : MonthYear

First expression (number of customers who bought something in the month):

Count(DISTINCT id_membre)

Now I tried to count My number of Custumers per months :

= Count({1<[date_client]={"<=MonthEnd"}>} DISTINCT id_membre)

But it don't works (I get 0).

[#8]

I think that the problem is that i need to "unlink/unselect" the MonthYear selection in my expression to count my customers ...

I tried something like that :

= Count({$<[date_client]={"<=MonthEnd"},MonthYear={""}>} DISTINCT id_membre)

To show number of customers at the end of each months ..... i'm pretty sure that : [date_client]={"<=MonthEnd"} is working !

But the problem is to unlink/unselect MonthYear, because it's the dimension of my table ...... i don't know if it's feasible.

Any ideas ?