Set analysis. Previous 30 days interval

[#1]

Hi there.

OK, after a number of hours going round the subject, I ask you to please help me with the following set analysis expression.

The data structure is as follows (illustrative):

Invoice_num	Date_field
10221	10/04/2014
10222	11/04/2014
10223	11/04/2014
10224	11/04/2014
10225	12/04/2014
10226	12/04/2014
10227	14/04/2014
10228	15/04/2014
10229	16/04/2014
10230	16/04/2014
10231	16/04/2014

Now, considering the most recent date in the Date_field I'd need to count how many invoices have been issued in the last 30 days. I.e. if the most recent date is 16/04/2014, I should be considering invoices issues from 17/03/2014 on.

I've found some similar unsolved questions and the expression I've managed to get is something like this:

Count({$<Date_field={">=$(=max(Date_field)-30)"}>}Date_field)

But it doesn't work. It returns 0.

I don't know if it has to do with data format or if it's just a syntax problem.

As always, many thanks in advance.

M.

[giakoum]

syntax looks correct

what is the date_field format?

[sunny_talwar · Accepted solution]

Try this:

Updated the formula for correct placement of parenthesis

=Count({$<Date_field={">=$(=Date(max(Date_field)-30, 'DD/MM/YYYY'))"}>}Date_field)

Need to make sure that formats are the same on both side. When you use Max on date, it changes to an integer value which needs to be changed back to date format.

HTH

Best,

Sunny

[fvelascog72]

Hi,

I have tried you solution and it´s works:

It could be date format as you comment.

[#5]

If you use the Max it converts date format into Number. If your Date_field not in number it returns 0. Please add date function to conert no into ur date format

Count({$<Date_field={">=$(=Date(max(Date_field)-30,'(urDateFormat)')"}>}Date_field)

[#6]

Thank you very much.

I'm only able to mark one reply as correct. Yours was correct as well though.

[#7]

Thank you very much for taking the time to check it.

Regards,

M.

[jim_chan]

Dear Sunny,

What should i add IF i have to look in in MTD?

For example, 1 of the dashboard is analyzed by MTD.

the bar chart needs to show number of outlets with at least 1 sale transaction in last 30 days. what else should i add in this set analysis?

Count({$<Date={">=$(=Date(max(Date)-30, 'DD/MM/YYYY'))"}>}CUST_ID)

[jim_chan]

Dear Sunny,

the MTD formula can be written as such rite??

Sum({<Year=, Month=, Week=,Date={">=$(=MonthStart(Date(Max(Date))))<=$(=Max(Date))"}>} SALES)

rgds

Jim

[jim_chan]

Dear Sunny,

Cani write like this??

Count({$<QTY_1 -= {'0'},Date={">=$(=MonthStart(Date(Max(Date))))<=$(=Max(Date)-30)"}>}CUST_ID)