topic Age Bucket with One Date Column in QlikView

Age Bucket with One Date Column

rahulsingh12 — Thu, 31 Aug 2017 15:27:32 GMT

hi,

I have a table with only one date column which is my po date,which gives when the po was created.

I have to create a age bucket for this date column.

PO PODate

1 1-aug-2017

2 3-aug-2017

3 7-aug-2017

4 10-aug-2017

I want a calendar where which will have date from min of PO date to date till max PO date.

So when a user selects any date from that calendar it will give me a day difference between the two dates.

How can this be achieved.

Regards,

Re: Age Bucket with One Date Column

Anil_Babu_Samineni — Thu, 31 Aug 2017 15:29:48 GMT

Then Create Calendar?

Re: Age Bucket with One Date Column

rahulsingh12 — Thu, 31 Aug 2017 15:31:08 GMT

i have to but how do I link it to the table?

If I link it to the table only the dates in the table will get picked up and the difference will be 0.

Re: Age Bucket with One Date Column

Anil_Babu_Samineni — Thu, 31 Aug 2017 15:38:51 GMT

rahul singh wrote:

So when a user selects any date from that calendar it will give me a day difference between the two dates.

May be explain this part

Re: Age Bucket with One Date Column

rahulsingh12 — Thu, 31 Aug 2017 15:44:55 GMT

I will give the calendar date as a selection field to the user.

Thus if the user selects 4-aug-2017 it will give

Podate 0-1days 2-8days 9-11days

1-aug-2017 0 3 0

3-aug-2017 1 0 0

Re: Age Bucket with One Date Column

effinty2112 — Thu, 31 Aug 2017 15:48:20 GMT

Hi Rahul,

Don't link your calendar to the table for this purpose. Create your calendar with data field called CalDate or something that is different from PODate.

Pick a date in this calendar and try this expression used in a chart with dimension PO to give the days between PODate and the selected date.

=Interval(PODate - CalDate,'D')

Cheers

Andrew

Re: Age Bucket with One Date Column

rahulsingh12 — Thu, 31 Aug 2017 16:00:46 GMT

Hi Andrew,

But I tried it is not working.

Regards,

Re: Age Bucket with One Date Column

Anil_Babu_Samineni — Thu, 31 Aug 2017 16:08:00 GMT

This is the case, I don't think you need any thing like Calendar and i believe you may need this?

SET DateFormat = DD/MM/YYYY;

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff] TT';

Load PO, PODate, Day, If(Day >= 0 and Day <= 1, Dual('0-1days',1), If(Day>=2 and Day <= 8, Dual('2-8days',2), If(Day >=9 and Day <=11,Dual('9-11days',3)))) as Day_Bucket;

Load PO, PODate, Day(PODate) as Day;

Load PO, Date(Date#(PODate,'DD-MMM-YYYY'),'DD/MM/YYYY') as PODate Inline [

PO, PODate

1 , 1-aug-2017

2 , 3-aug-2017

3 , 7-aug-2017

4 , 10-aug-2017

];

Dimensions are PODate and

If(Day >= 0 and Day <= 1, Dual('0-1days',1), If(Day>=2 and Day <= 8, Dual('2-8days',2), If(Day >=9 and Day <=11,Dual('9-11days',3))))

// Day_Bucket

Expression is Sum(PO)

Note - I think you need here transpose from Column to Row in Pivot Report for Calc. Dim

Re: Age Bucket with One Date Column

rahulsingh12 — Thu, 31 Aug 2017 21:57:50 GMT

If I had to calculate the age for 2nd august then it won't be possible without calendar as there is no 2nd august in the table

so i think calendar is needed

Re: Age Bucket with One Date Column

rahulsingh12 — Thu, 31 Aug 2017 22:00:04 GMT

Hi Andrew,

If there is no link in calendar and table then if a user selects a filter (which is a calendar date) then PO date will not be selected for that period.

Only the ageing will be calculated.

regards,