topic Re: Add 30, 60 & 90 day date filters based on Date feild in QlikView

Add 30, 60 & 90 day date filters based on Date feild

amit_saini — Wed, 15 Apr 2015 11:55:02 GMT

Hi Folks ,

Looking for something like below, from today() date last 30 , 60 and 90 days filter in list box.

0 to 30 ->30

0 to 60->60

0 to 90 ->90

Kindly help!

Thanks,

Re: Add 30, 60 & 90 day date filters based on Date feild

Anonymous — Wed, 15 Apr 2015 12:12:22 GMT

Do you want to select last 30, 60 days of your calendar on the basis of filter selection?

Re: Add 30, 60 & 90 day date filters based on Date feild

MK_QSL — Wed, 15 Apr 2015 13:48:45 GMT

use this expression to create list box

=Aggr(Class(Today()-Date,30),Date)

=Aggr(DUAL(Replace(Class(Today()-Date,30),'<= x <','-'),Class(Today()-Date,30)),Date)

=Class(Today()-Date,30)

=DUAL(Replace(Class(Today()-Date,30),'<= x <','-'),Class(Today()-Date,30))

Re: Add 30, 60 & 90 day date filters based on Date feild

amit_saini — Thu, 16 Apr 2015 04:48:56 GMT

Hi Manish ,

Thanks for you suggestion but please help me below:

Script:

LOAD

Datefield as %DateID,

$(vToday)-floor(num(Datefield)) AS DaysDifference,

From

....

Inside list box I'm calling this expression :

=if(DaysDifference > 0 and DaysDifference <=30, '30',

if(DaysDifference > 0 AND DaysDifference <= 60, '60',

if(DaysDifference > 60 , '90',

)))

Now I'm having 30,60,90 options inside list box, when I click on 30 it is giving me back 30 days from today, but when I'm clicking on 60 it is not giving me desired result .i.e from today past 60 days and same with 90.

Kindly help!

Thanks,

Re: Add 30, 60 & 90 day date filters based on Date feild

MK_QSL — Thu, 16 Apr 2015 05:31:18 GMT

=if(DaysDifference > 0 and DaysDifference <=30, '30',

if(DaysDifference > 30 AND DaysDifference <= 60, '60',

if(DaysDifference > 60 , '90',

)))

Re: Add 30, 60 & 90 day date filters based on Date feild

amit_saini — Thu, 16 Apr 2015 05:44:03 GMT

Sorry Still the same result.

Please see attachment for desired result.

Thanks,

Re: Add 30, 60 & 90 day date filters based on Date feild

MK_QSL — Thu, 16 Apr 2015 06:32:13 GMT

Provide your sample application...or your sample date data...

Re: Add 30, 60 & 90 day date filters based on Date feild

amit_saini — Thu, 16 Apr 2015 06:41:33 GMT

Please see the attachment and Compare Bucket with Date.

Thanks,
AS

Re: Add 30, 60 & 90 day date filters based on Date feild

MK_QSL — Thu, 16 Apr 2015 07:19:56 GMT

Use this expression to create list box

=Aggr(

if(DaysDifference > 0 and DaysDifference <=30, '30',

if(DaysDifference > 30 AND DaysDifference <= 60, '60',

if(DaysDifference > 60 , '90')))

,%DateID)

Re: Add 30, 60 & 90 day date filters based on Date feild

Thu, 31 Mar 2016 08:01:34 GMT

Hi Amit,

How did you overcome this query, I am also facing the same problem if you have solution just share with me.

Thanks,

Ashok.

Re: Add 30, 60 & 90 day date filters based on Date feild

amit_saini — Thu, 31 Mar 2016 08:13:17 GMT

Hi Ashok,

I did something like below , some cosmetic changes with 3 different list box and calling individual expressions:

30 ---=if(DaysDifference >= 0 and DaysDifference <=30, '30')

60---=if(DaysDifference >= 0 and DaysDifference <=60, '60')

90---=if( DaysDifference <=90, '90')

Thanks,

Re: Add 30, 60 & 90 day date filters based on Date feild

Thu, 31 Mar 2016 09:30:32 GMT

Hi,

Thanks for your quick response.

But in my case i want to use as Dimension in my straight table.

like below

Time Period	Loads	Cost
Last 7 days	10	20
Last 14 days	20	40
Last 30 days	50	100
Last 12 months	100	200

If you any suggestions on same just let me know.

Thanks,

Ashok.

Re: Add 30, 60 & 90 day date filters based on Date feild

Fri, 01 Apr 2016 08:15:57 GMT

Hi Amit,

I got the solution for above query. please see below steps.

1.Create inline table and it's a don't have any connection with other tables(Like Data iland)

LOAD * inline [

Bucket

0-7

0-14

0-30

];

2. Then take Bucket as a dimension in chart.

3. Write the expressions like below.

=if(Bucket='0-7' , Sum({<Date={">=$(=Date(Today()-7))<=$(=Today())"}>}Load),

if(Bucket='0-14' , Sum({<Date={">=$(=Date(Today()-14))<=$(=Today())"}>}Load),

if(Bucket='0-30' , Sum({<Date={">=$(=Date(Today()-30))<=$(=Today())"}>}Load),

)))

This will work in your scenario also. For more clarification i am attaching application as well.

Thanks,

Ashok.

Re: Add 30, 60 & 90 day date filters based on Date feild

rabbani_sk — Wed, 14 Mar 2018 09:53:21 GMT

Use the below code in the Load script,

If ( Date(Today()) - Date(Date_Field) ) <= 30, '30days',

If ( Date(Today()) - Date(Date_Field) ) <= 60, '60days',

If ( Date(Today()) - Date(Date_Field) ) <= 90, '90days' ) ) ) as Days_Filter

Re: Add 30, 60 & 90 day date filters based on Date feild

HariniAnalyst — Thu, 31 Oct 2024 13:58:15 GMT

I didn’t receive the solution I expected. Instead of using today’s date, I used the claim submission date. Based on the user’s selection in the claim submission date filter, the buckets should display the intervals of 0-30 days and 31-60 days from the selected date.

Thanks in advance