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: 
Highlighted
Creator
Creator

How to create the 3 months and 6 months bucket

Hi all,

  I have stuck one place please give suggestion any one  on this.

My requirement is I have to give selection 3 months bucket and 6 month bucket,

in the edit script I wrote some logic but I am getting 3 months bucket properly  but I am not getting 6 month bucket properly.

I need to display the 3 months(90) days data from 0 to 90 days.

I need  to display the 6 months (180) days data from 0 to 180 days.

the problem I m getting when I select 6 month it is selecting from 91 to 180(90-180) days but It need  to select  0 to 180 days selection.

Please give any suggestion on this.

please find the attachments.

Thanks in advance

1 Solution

Accepted Solutions
Highlighted

Re: How to create the 3 months and 6 months bucket

Hi,

one solution could be:

QlikCommunity_Message_140487_Pic1.JPG.jpg

QlikCommunity_Message_140487_Pic2.JPG.jpg

QlikCommunity_Message_140487_Pic3.JPG.jpg

QlikCommunity_Message_140487_Pic4.JPG.jpg

tabSales:

LOAD *,

    Today()-Date as Numberofdays,

    Money(Round(Rand()*1000,0.01)) as Sales;

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

AutoGenerate 100;

 

tabIntervals:

LOAD Distinct

  Numberofdays,

  Dual('0-'&90*pow(2,5-IterNo())&' days',90*pow(2,5-IterNo())) as DaysBucket

Resident tabSales

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

hope this helps

regards

Marco

View solution in original post

7 Replies
Highlighted
MVP
MVP

Re: How to create the 3 months and 6 months bucket

year:

LOAD

  year,

  Interval(Today()-num(year),'d') as Numberofdays,

     sales

FROM

Monthbucket.xlsx

(ooxml, embedded labels, table is Sheet1);

Load

  *,

  if(Numberofdays <=90, '3Months') as MonthBucket

Resident year;

Load

  *,

  if(Numberofdays >0 and Numberofdays <= 180, '6Months') as MonthBucket

Resident year;

DROP Table year;

Highlighted

Re: How to create the 3 months and 6 months bucket

Hi,

one solution could be:

QlikCommunity_Message_140487_Pic1.JPG.jpg

QlikCommunity_Message_140487_Pic2.JPG.jpg

QlikCommunity_Message_140487_Pic3.JPG.jpg

QlikCommunity_Message_140487_Pic4.JPG.jpg

tabSales:

LOAD *,

    Today()-Date as Numberofdays,

    Money(Round(Rand()*1000,0.01)) as Sales;

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

AutoGenerate 100;

 

tabIntervals:

LOAD Distinct

  Numberofdays,

  Dual('0-'&90*pow(2,5-IterNo())&' days',90*pow(2,5-IterNo())) as DaysBucket

Resident tabSales

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

hope this helps

regards

Marco

View solution in original post

Highlighted

Re: Re: How to create the 3 months and 6 months bucket

another solution could be:

tabSales:

LOAD *,

    Today()-Date as Numberofdays,

    Money(Round(Rand()*1000,0.01)) as Sales;

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

AutoGenerate 100;

  

tabIntervals:

LOAD Distinct

  Date,

  Dual(13-IterNo()&' Months',13-IterNo()) as MonthBucket

Resident tabSales

While AddMonths(Date,13-IterNo())>=Today();

QlikCommunity_Message_140484_Pic5.JPG.jpg

hope this helps

regards

Marco

Highlighted

Re: Re: Re: How to create the 3 months and 6 months bucket

or

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);

hope this helps

regards

Marco

Highlighted

Re: Re: How to create the 3 months and 6 months bucket

Hi,

Or another way also with date functions and then create buckets

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-6Buckets2.png

Ragards

Anand

Highlighted
Creator
Creator

Re: How to create the 3 months and 6 months bucket

Thank you very much Marco wedel,

Now it is working fine...

Highlighted
Creator
Creator

Re: How to create the 3 months and 6 months bucket

Thank you very much for helping Anand Chouhan,

  It is working fine.