Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for
Did you mean:
Not applicable

## Set analysis. Previous 30 days interval

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.

1 Solution

Accepted Solutions
MVP

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

12 Replies
Partner - Master II

syntax looks correct

what is the date_field format?

MVP

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

Partner - Specialist

Hi,

I have tried you solution and it´s works:

It could be date format as you comment.

Not applicable
Author

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)

Not applicable
Author

Thank you very much.

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

Not applicable
Author

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

Regards,

M.

Specialist

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)

Specialist

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

Specialist

Dear Sunny,

Cani write like this??

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

Community Browser