Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
paulwalker
Creator II
Creator II

Need help?

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:

DateSales Qty
1/22/2011132
1/23/201120
1/24/201196
1/25/201134
1/26/2011138
1/27/201167
1/28/201159
1/29/201178
1/30/201136
1/31/201187
2/1/20116
2/2/2011100
2/3/201318
2/4/201187
2/5/201136
5/7/201499
5/8/201436
5/9/201430
5/10/201490
5/11/201412
5/12/201448
5/13/20149
5/14/201430
5/15/201415

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

Thanks in advance........

10 Replies
nagaiank
Specialist III
Specialist III

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

];

MarcoWedel

Hi,

one solution using chart functions:

QlikCommunity_Thread_130502_Pic1.JPG.jpg

QlikCommunity_Thread_130502_Pic3.JPG.jpg

QlikCommunity_Thread_130502_Pic4.JPG.jpg

QlikCommunity_Thread_130502_Pic2.JPG.jpg

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

Not applicable

Hi,

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

If so, see the attache file.

WanKi,

MarcoWedel

selector list box

QlikCommunity_Thread_130502_Pic5.JPG.jpg

QlikCommunity_Thread_130502_Pic6.JPG.jpg

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

hope this helps

regards

Marco

paulwalker
Creator II
Creator II
Author

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.....

paulwalker
Creator II
Creator II
Author

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..

jagan
Luminary Alumni
Luminary Alumni

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.

Not applicable

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,

paulwalker
Creator II
Creator II
Author

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....