topic Re: Re: Need Help?? in QlikView

Need Help??

paulwalker — Thu, 18 Sep 2014 11:04:10 GMT

Hi Community,

How to calculate Bucket based on date

(0-30 days, 30-60 days, 60-90 days and Above 90 days) but based on Date Field

I need only 0-30, 31-60, 61-90, Above 90......

suppose i have 2012, 2013, 2014 years data

if i am click 2012 i want to show this year data (0-30, 31-60, 61-90, Above 90......)

same as 2013 and 2014

by default current year data..

Here 0-30 days means (suppose today 9/18/2014 = back 30 days means 8/20/2014)

see like this

9/18/2014

9/17/2014

9/16/2014

9/15/2014

9/14/2014

9/13/2014

9/12/2014

9/11/2014

9/10/2014

9/9/2014

9/8/2014

9/7/2014

9/6/2014

9/5/2014

9/4/2014

9/3/2014

9/2/2014

9/1/2014

8/31/2014

8/30/2014

8/29/2014

8/28/2014

8/27/2014

8/26/2014

8/25/2014

8/24/2014

8/23/2014

8/22/2014

8/21/2014

8/20/2014

Hope you Understand...same as 31-60, 61-90 and Above 90 (Above 90 means 91 days to 365 days)

please help me...

Re: Need Help??

maxgro — Thu, 18 Sep 2014 11:11:36 GMT

use a calculated dimension with class function and the difference between today and date

=class(today() - Date,30)

Re: Need Help??

paulwalker — Thu, 18 Sep 2014 11:19:29 GMT

I't ok,

But we are using CLASS function...

It showing like this...

But i don't need all 0-30, 31-60, 61-90 and Above 90(Above 90 means 91 to 365 days)

When i am open my application i want show Current...

and if i am select 2012 year, i want to show 2012 year data... based on selection..

Hope you understand..........

Re: Need Help??

paulwalker — Thu, 18 Sep 2014 12:36:45 GMT

Please anyone can help me............

Re: Need Help??

Thu, 18 Sep 2014 14:49:16 GMT

Use the expression above in an if statement and define the dimension value with hard coded text:

E.g. if(date#(Date)-today() <=30, '0 to 30',

if(date#(Date)-today()>30 and date#(Date)-today()<=60, '31 to 60',

if(date#(Date)-today() >90 and date#(Date)-today() <='90', '61 to 90'

'>90'

)))

then either....

as Aging_Category, if in a script, or just use as an expression in a calculated dimension. You might not need to the date# bit, depends what type your data is.

Re: Need Help??

maleksafa — Thu, 18 Sep 2014 14:55:41 GMT

check this link:

http://community.qlik.com/blogs/qlikviewdesignblog/2014/07/14/buckets

Re: Need Help??

jagan — Thu, 18 Sep 2014 15:00:19 GMT

Hi Paul,

Try like this in script

LOAD

if((DateFieldName)-today() >90, 'Above 90',

if((DateFieldName)-today() > 60, '61 to 90',

if((DateFieldName)-today() > 30, '31 to 60',

if((DateFieldName)-today() <=30, '0 to 30')))) AS Aging

FROM DataSource;

Hope this helps you.

Regards,

Jagan.

Re: Re: Need Help??

paulwalker — Thu, 18 Sep 2014 15:03:14 GMT

Hi Watson,

Thanks for response..

I have created bucket but it showing Above 90,

But i have data 0-30, 31-60 and 61-90

it's not showing properly.....

PFA,

Re: Re: Need Help??

paulwalker — Thu, 18 Sep 2014 15:44:40 GMT

Hi Jagan,

Please check this application..

it showing wrong data..

Here 0-30 --> back 30 days

31-60--> back 31 day-60 days...

Re: Need Help??

Thu, 18 Sep 2014 15:54:52 GMT

Hello,

Please check attached. Not sure if you want it this way.

Re: Re: Need Help??

paulwalker — Thu, 18 Sep 2014 16:30:59 GMT

HI Amit,

This is Perfect,....

if ( (Today() - Date) >= 90, 'Over 90',

if ((Today() - Date) >= 60, 'Over 60',

if ((Today() - Date) >= 30, 'Over 30' , 'Above 90'))) as Bucket;

i need like this.... please try once

why it's showing wrong.....

Re: Need Help??

sundarakumar — Thu, 18 Sep 2014 17:03:56 GMT

I dont think u should be doing this on the back end. bec u want the last 90 days details with respect to the user selection. When u write it in backend script it will refer only today's date to set bucket which would be wrong in ur case. so write the same in calculated dimension.

Sorry i have personal edition now, could not see what u have posted as example.

-Sundar

Re: Need Help??

Thu, 18 Sep 2014 17:12:15 GMT

Hi Paul,

Why do you want to show it in If condition ? Is there any issue with the solution I have given you ?

Thanks

Amit

Re: Need Help??

jagan — Fri, 19 Sep 2014 03:37:05 GMT

Hi Paul,

Try this

Bucket:

Load *, if(today() - (Date) >90, 'Above 90',

if(today() - (Date) > 60, '61 to 90',

if(today() - (Date) > 30, '31 to 60',

if(today() - (Date) <=30, '0 to 30')))) as Bucket;

LOAD Date,

Year(Date) as Year,

Month(Date) as Month,

Sales

FROM

(ooxml, embedded labels, table is Sheet1);

Regards,

Jagan.

Re: Need Help??

Anonymous — Fri, 19 Sep 2014 06:32:01 GMT

Hi Paul,

See the attached qvw .

Hope this would be helpful for you.

Regards

Manoj

Re: Need Help??

paulwalker — Wed, 24 Sep 2014 14:01:52 GMT

Hi All,

Above 90 means---> not current year

suppose i have 2014 data.. here above 90 means --> i want to show all the data 2013 and so on..

only current data to display 0-30, 30-60, 60-90 and Above90

if i am click 2013 year it should be ABOVE 90

2012 also........

Re: Need Help??

Wed, 24 Sep 2014 14:21:09 GMT

Hi Paul,

Try this

Bucket:

Load *, if(today() - (Date) >90, 'Above 90',

if(today() - (Date) > 60 and if(today() - (Date)= <90, '61 to 90',

if(today() - (Date) > 30 and if(today() - (Date) =< 60, '31 to 60',

if(today() - (Date) <=30 and if(today() - (Date) =<0, '0 to 30')))) as Bucket;

LOAD Date,

Year(Date) as Year,

Month(Date) as Month,

Sales

FROM

(ooxml, embedded labels, table is Sheet1);

Re: Need Help??

Nagaraju_KCS — Wed, 24 Sep 2014 16:48:49 GMT

Hi Amit,

This is very helpful to me..

i need one help...

i have same requirement...but Above90

Here Above90 means not only current year..

Suppose i have 2014 year data.... it should be all (0-30, 30-60, 60-90 and Above90)

if i am clock 2013 it should be only Above90.....

0-30--> current date-last 30 days

30-60----> 31 to 60 days

60-90--> 61 to 90 days

Above90--> 91 days to.. last record...

Above90 not only current year data.. suppose i have 2009, 2010,2011,2012,2013,2014 years data..

0-30-->2014 data

30-60--> 2014 data

60-90--->2014 data

Above90--> above 90 days all years data

if i am click any 2009, 2010,2011,2012,2013 year it should be... Above90

please anyone can help me...........

It's very urgent...............................

Thanks in Advance............

Re: Need Help??

Thu, 25 Sep 2014 08:51:23 GMT

Hello,

You can try following.

Replace the Load statement in my above example with

Directory;

LOAD Date,

Sales,

Year(Date) as Year,

num(Today()-MakeDate(Year(Date),Month(Date),Day(Date))) as Days

FROM

[New Microsoft Office Excel Worksheet.xlsx]

(ooxml, embedded labels, table is Sheet1);

and Interval Match should be

BucketInterval:

LOAD * INLINE [

MinBucket ,MaxBucket, BucketCategory

0, 30, '0-30 days'

31, 60,'31-60 days'

61, 90,'61-90 days'

91, max(Days),'Above 90 days'

];

Thanks

Amit Prabhudesai

Re: Need Help??

Nagaraju_KCS — Thu, 25 Sep 2014 17:02:33 GMT

Thank You very much Amit.....................