topic Re: Set analysis. Previous 30 days interval in QlikView

Set analysis. Previous 30 days interval

Mon, 15 Jun 2015 14:48:02 GMT

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.

Re: Set analysis. Previous 30 days interval

giakoum — Mon, 15 Jun 2015 14:52:40 GMT

syntax looks correct

what is the date_field format?

Re: Set analysis. Previous 30 days interval

sunny_talwar — Mon, 15 Jun 2015 14:53:22 GMT

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

Re: Set analysis. Previous 30 days interval

fvelascog72 — Mon, 15 Jun 2015 14:53:27 GMT

Hi,

I have tried you solution and it´s works:

It could be date format as you comment.

Re: Set analysis. Previous 30 days interval

Mon, 15 Jun 2015 14:56:51 GMT

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)

Re: Set analysis. Previous 30 days interval

Tue, 16 Jun 2015 09:18:56 GMT

Thank you very much.

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

Re: Set analysis. Previous 30 days interval

Tue, 16 Jun 2015 09:19:26 GMT

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

Regards,

Re: Set analysis. Previous 30 days interval

jim_chan — Tue, 24 Jan 2017 23:35:32 GMT

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)

Re: Set analysis. Previous 30 days interval

jim_chan — Tue, 24 Jan 2017 23:46:04 GMT

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

Re: Set analysis. Previous 30 days interval

jim_chan — Tue, 24 Jan 2017 23:52:15 GMT

Dear Sunny,

Cani write like this??

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

Re: Set analysis. Previous 30 days interval

sunny_talwar — Wed, 25 Jan 2017 02:15:57 GMT

Would you be able to create a new thread and add some sample data and explain what output you are looking to get Jim?

Re: Set analysis. Previous 30 days interval

jim_chan — Wed, 25 Jan 2017 02:24:53 GMT

ok i gonna create new thread.

Re: Set analysis. Previous 30 days interval

sunny_talwar — Wed, 25 Jan 2017 02:26:21 GMT

Awesome, thanks!!!