Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead 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_numDate_field
1022110/04/2014
1022211/04/2014
1022311/04/2014
1022411/04/2014
1022512/04/2014
1022612/04/2014
1022714/04/2014
1022815/04/2014
1022916/04/2014
1023016/04/2014
1023116/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
sunny_talwar

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

View solution in original post

12 Replies
giakoum
Partner - Master II
Partner - Master II

syntax looks correct

what is the date_field format?

sunny_talwar

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
Partner - Specialist
Partner - Specialist

Hi,

I have tried you solution and it´s works:

Captura_38.png

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.

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

jim_chan
Specialist
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

jim_chan
Specialist
Specialist

Dear Sunny,

Cani write like this??

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