topic Re: Need help? in QlikView

Need help?

paulwalker — Sat, 23 Aug 2014 11:04:36 GMT

Hi All,

How to calculate Bucket based on date

(0-30 days, 30-60 days, 60-90 days and Above 90 days) but based on current year..

Ex:

Date	Sales Qty
1/22/2011	132
1/23/2011	20
1/24/2011	96
1/25/2011	34
1/26/2011	138
1/27/2011	67
1/28/2011	59
1/29/2011	78
1/30/2011	36
1/31/2011	87
2/1/2011	6
2/2/2011	100
2/3/2013	18
2/4/2011	87
2/5/2011	36
5/7/2014	99
5/8/2014	36
5/9/2014	30
5/10/2014	90
5/11/2014	12
5/12/2014	48
5/13/2014	9
5/14/2014	30
5/15/2014	15

here i want show current year data only.....based on year selection..

Thanks in advance........

Re: Need help?

nagaiank — Sat, 23 Aug 2014 12:53:46 GMT

You may try:

LOAD *,

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

Where Year(Date) = Year(Today());

LOAD * Inline [

Date,Sales Qty

1/22/2011,132

1/23/2011,20

1/24/2011,96

1/25/2011,34

1/26/2011,138

1/27/2011,67

1/28/2011,59

1/29/2011,78

1/30/2011,36

1/31/2011,87

2/1/2011,6

2/2/2011,100

2/3/2013,18

2/4/2011,87

2/5/2011,36

5/7/2014,99

5/8/2014,36

5/9/2014,30

5/10/2014,90

5/11/2014,12

5/12/2014,48

5/13/2014,9

5/14/2014,30

5/15/2014,15

];

Re: Need help?

MarcoWedel — Sat, 23 Aug 2014 13:35:21 GMT

Hi,

one solution using chart functions:

LOAD *,
    year(Date) as year,
    month(Date) as month,
    week(Date) as week,
    day(Date) as day;
LOAD Date(Date#('01/01/2011', 'MM/DD/YYYY')+IterNo()-1) as Date,
    Money(Rand()*1000) as [Sales Qty]
AutoGenerate 1
While Date#('01/01/2011', 'MM/DD/YYYY')+IterNo()-1 <= Today();
//FROM [http://community.qlik.com/thread/130502]
//(html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

Re: Need help?

Sat, 23 Aug 2014 14:07:30 GMT

Hi,

Maybe you want to make groups(bucket) of dimension, right ?

If so, see the attache file.

WanKi,

Re: Re: Need help?

MarcoWedel — Sat, 23 Aug 2014 15:03:15 GMT

selector list box

=If(year=year(Today()), Dual(PurgeChar(Class(Today()-Date, 30, '-'),'<='), Class(Today()-Date, 30)))

hope this helps

regards

Marco

Re: Need help?

paulwalker — Sat, 23 Aug 2014 15:25:47 GMT

HI Marco,

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

(here above 90 means 90-365 days for only current year...)

and same way previous year..

Here comparing both current Vs previous year

Hope you Understand.....

Re: Need help?

paulwalker — Mon, 25 Aug 2014 05:26:39 GMT

Hi Marco,

Thanks for your response...

i need only 0-30, 30-60, 60-90 and above90 (means 91 days-365 days)

this is for only current year..

Re: Need help?

jagan — Mon, 25 Aug 2014 05:33:51 GMT

Hi Paul,

Try like this

LOAD

If(DaysDiff > 90, '90+',

If(DaysDiff > 60, '60-90',

If(DaysDiff > 30, '30-60', '0-30'))) AS Bucket;

LOAD *,

Today()-Date as DaysDiff

Where Year(Date) = Year(Today());

LOAD *

FROM DataSource;

Hope this helps you.

Regards,

Jagan.

Re: Re: Need help?

Mon, 25 Aug 2014 15:23:10 GMT

Hi,

What about separating into two tables, Previous and Current ?

It's hard for me to make it.

Because I am a newbie for QlikView ~ . ^^

I Hope this helps you even a little.

WanKi,

Re: Need help?

paulwalker — Mon, 08 Sep 2014 10:39:58 GMT

Hi NagaianK,

How change based on year selection,

suppose if i am select 2004 year, i want to show 2014 and 2013

if i am select 2010 year, i want to show 2010 and 2009

i want to show current year and previous year....

Re: Need help?

paulwalker — Thu, 18 Sep 2014 07:23:27 GMT

Hi Macro,

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

8/20/2014

8/21/2014

8/22/2014

8/23/2014

8/24/2014

8/25/2014

8/26/2014

8/27/2014

8/28/2014

8/29/2014

8/30/2014

8/31/2014

9/1/2014

9/2/2014

9/3/2014

9/4/2014

9/5/2014

9/6/2014

9/7/2014

9/8/2014

9/9/2014

9/10/2014

9/11/2014

9/12/2014

9/13/2014

9/14/2014

9/15/2014

9/16/2014

9/17/2014

9/18/2014

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

Hope you Understand...

please help me...