Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator
Creator

Month wise Bucket

Hi All,

how to create buckets month wise i want to see current month to previous three months one bucket and current month to previous six months second bucket ,Please find the attached excel.

could u please help on this.

Advance Thanks

Munna

Thanks,
Srinivas
6 Replies
Highlighted
MVP
MVP

Re: Month wise Bucket

Temp:

Load

  *,

  Interval(Today()-Date,'d') as NumberofDays;

Load

  Date(Date#(Year,'DD/MM/YYYY')) as Date,

  sales

Inline

[

  Year, sales

  01/01/2014, 10

  01/02/2014, 20

  01/03/2014, 30

  01/04/2014, 40

  01/05/2014, 50

  01/06/2014, 60

  01/07/2014, 70

  01/08/2014, 80

  01/09/2014, 90

  01/10/2014, 100

  01/11/2014, 110

  01/12/2014, 120

];

Final:

Load

  *,

  If(NumberofDays > 0 and NumberofDays <=90, '3Months') as Bucket

Resident Temp;

Load

  *,

  If(NumberofDays > 0 and NumberofDays <=180, '6Months') as Bucket

Resident Temp;

Drop Table Temp;

Highlighted
Not applicable

Re: Month wise Bucket

Just re-use Manish's scripts, you can do the calculation on monthly interval.

Temp:

Load

  *,

  Interval(Month(Today())-Month(Date)) as NumberOfMonths;

Load

  Date(Date#(Year,'DD/MM/YYYY')) as Date,

  sales

Inline

[

  Year, sales

  01/01/2014, 10

  01/02/2014, 20

  01/03/2014, 30

  01/04/2014, 40

  01/05/2014, 50

  01/06/2014, 60

  01/07/2014, 70

  01/08/2014, 80

  01/09/2014, 90

  01/10/2014, 100

  01/11/2014, 110

  01/12/2014, 120

];

Final:

Load

  *,

  If(NumberOfMonths >=0 and NumberOfMonths <=2, '3Months') as Bucket //3 month bucket include current month

Resident Temp;

Load

  *,

  If(NumberOfMonths >=0 and NumberOfMonths <=5, '6Months') as Bucket //6 month bucket include current month

Resident Temp;

Drop Table Temp;

Highlighted

Re: Month wise Bucket

Hi,

Another way with the help of date functions we can achieve this

Temp:

Load

  Date(Date#(Year,'DD/MM/YYYY')) as Date,

  sales

Inline

[

  Year, sales

  01/01/2014, 10

  01/02/2014, 20

  01/03/2014, 30

  01/04/2014, 40

  01/05/2014, 50

  01/06/2014, 60

  01/07/2014, 70

  01/08/2014, 80

  01/09/2014, 90

  01/10/2014, 100

  01/11/2014, 110

  01/12/2014, 120

];

Final:

LOAD Distinct *,

if(Date > AddMonths( Today(), -3 ) and Date <= Today(),'3Months') as Bucket

Resident Temp;

LOAD Distinct * ,

if(Date > AddMonths( Today(), -6 ) and Date <= Today(),'6Months') as Bucket

Resident Temp;

DROP Table Temp;

Let me know if any changes required, And you get.

3-6Buckets.png

Regards

Anand

Highlighted

Re: Month wise Bucket

Hi,

one solution could be:

QlikCommunity_Message_140484_Pic6.JPG.jpg

QlikCommunity_Message_140484_Pic7.JPG.jpg

tabSales:

LOAD *,

    Today()-Date as Numberofdays,

    Money(Ceil(Rand()*1000),'$#,##0;($#,##0)') as Sales;

LOAD Date(MakeDate(2010)+Floor(Rand()*(Today()-MakeDate(2010)))) as Date

AutoGenerate 100;

  

tabIntervals:

LOAD Distinct

  Numberofdays,

  Dual(3*pow(2,5-IterNo())&' Months',90*pow(2,5-IterNo())) as MonthBucket

Resident tabSales

While 90*pow(2,5-IterNo())>=RangeMax(Numberofdays,90);

QlikCommunity_Message_140484_Pic8.JPG.jpg

hope this helps

regards

Marco

Highlighted

Re: Month wise Bucket

Hi,

you might wish to follow this conversation as well:

How to create the 3 months and 6 months bucket

regards

Marco

Highlighted
Creator
Creator

Re: Month wise Bucket

Hi All ,

i am using this num(month([Due Date]),'00') as Month_1 for numbers ,

using buckets how to create 3months,6months,9months,1year and 1>year

any suggestions,

thanks