Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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........
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
];
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
Hi,
Maybe you want to make groups(bucket) of dimension, right ?
If so, see the attache file.
WanKi,
selector list box
=If(year=year(Today()), Dual(PurgeChar(Class(Today()-Date, 30, '-'),'<='), Class(Today()-Date, 30)))
hope this helps
regards
Marco
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.....
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..
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.
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,
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....