Age Bucket with One Date Column

[rahulsingh12]

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,

[Anil_Babu_Samineni]

Then Create Calendar?

[rahulsingh12]

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.

[Anil_Babu_Samineni]

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

[rahulsingh12]

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

[effinty2112]

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

[rahulsingh12]

Hi Andrew,

But I tried it is not working.

Regards,

[Anil_Babu_Samineni]

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

OR

// Day_Bucket

Expression is Sum(PO)

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

[rahulsingh12]

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

[rahulsingh12]

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,