Data Table

[ColtE8]

I am trying to create a table that shows how many milestones are within a certain timeline. I believe what is making this tricky is wanting all of the timelines to show, even if there are zero milestones within that timeline. The different timelines are 0 to 7 days, 8 to 15 days, 15 to 30 days, 31 days or more, and overdue (due date is before today's date). The current syntax I have but isn't working is:

if([Issue Type] = 'Milestone' and "Current Estimated Completion Date" - today() <0, 1,0) as milestone_age_group_overdue,
if([Issue Type] = 'Milestone' and "Current Estimated Completion Date" - today() >0 and ([Issue Type] = 'Milestone' and "Current Estimated Completion Date" - today() <8), 1,0) as milestone_age_group_0_to_7_Days,
if([Issue Type] = 'Milestone' and "Current Estimated Completion Date" - today() >7 and ([Issue Type] = 'Milestone' and "Current Estimated Completion Date" - today() <15), 1,0) as milestone_age_group_8_to_14_Days,
if([Issue Type] = 'Milestone' and "Current Estimated Completion Date" - today() >13 and ([Issue Type] = 'Milestone' and "Current Estimated Completion Date" - today() <31), 1,0) as milestone_age_group__15_to_30_Days,
if([Issue Type] = 'Milestone' and "Current Estimated Completion Date" - today() >30, '31 Days or More') as milestone_age_group_31_Days_or_More,

I'm putting this into a pivot table as well but it just doesn't seem to be working properly. I've been using Qlik for about 2 weeks and coding for about that long as well so any advice is great!

[ajaykakkar93]

Hi,

In this usecase ill first calculate the difference "Current Estimated Completion Date" - today() and keep a separate column, Then in preceding load ill used the difference column with the condition 

sample code

Just add your condition for [Issue Type] = 'Milestone' 

 if([Pending Days] >= 0 and [Pending Days] <= 30, '00-30 Days',
    if([Pending Days] >= 31 and [Pending Days] <= 45, '31-45 Days',
    if([Pending Days] >= 46 and [Pending Days] <= 60, '46-60 Days',
    if([Pending Days] >= 61 and [Pending Days] <= 90, '61-90 Days',
    if([Pending Days] >= 91 and [Pending Days] <= 120, '91-120 Days',
    if([Pending Days] >= 121 and [Pending Days] <= 150, '121-150 Days',
    if([Pending Days] >= 151 and [Pending Days] <= 180, '151-180 Days',
    if([Pending Days] >= 180, 'Above 180 Days'
    )))))))) as [Overdue Bucket],
    if([Pending Days] < [Ready for Delivery in Days], 'Not Due Amount' ,
    	if([Pending Days] = [Ready for Delivery in Days], 'Due Amount',
    	if([Pending Days] >= [Ready for Delivery in Days], 'Overdue Amount','No PPIC or Sale Date')
    )) as [Due Type],

[ColtE8]

Thank you! But once I input the issue types then that's essentially what we already have once I type it all out. And I also need it to be un-nested so that each day range shows up even if there aren't any milestones that fall within a particular day range.

I essentially need a table that shows along the lines of (made up numbers):

Day range | Milestone Count

Overdue | 0

0 to 7 Days | 14

8 to 14 Days | 0

15 to 30 Days | 5

30 Days or More | 21

[ajaykakkar93]

what i understand is you need to get 0 value also if value isnt present??

[ColtE8]

Yes that's correct. 

Right now I have:

if([Issue Type] = 'Milestone' and [Status] = 'Open' and "Current Estimated Completion Date" - today() <0, 1,0) as milestone_age_group_overdue,
if([Issue Type] = 'Milestone' and "Current Estimated Completion Date" - today() >0 and ([Issue Type] = 'Milestone' and "Current Estimated Completion Date" - today() <8), 1,0) as milestone_age_group_0_to_7_Days,
if([Issue Type] = 'Milestone' and "Current Estimated Completion Date" - today() >7 and ([Issue Type] = 'Milestone' and "Current Estimated Completion Date" - today() <15), 1,0) as milestone_age_group_8_to_14_Days,
if([Issue Type] = 'Milestone' and "Current Estimated Completion Date" - today() >13 and ([Issue Type] = 'Milestone' and "Current Estimated Completion Date" - today() <31), 1,0) as milestone_age_group__15_to_30_Days,
if([Issue Type] = 'Milestone' and "Current Estimated Completion Date" - today() >30, '31 Days or More') as milestone_age_group_31_Days_or_More,

Pick(Match(RangeMax(milestone_age_group_overdue, milestone_age_group_0_to_7_Days, milestone_age_group_8_to_14_Days, milestone_age_group__15_to_30_Days, milestone_age_group_31_Days_or_More), milestone_age_group_overdue, milestone_age_group_0_to_7_Days, milestone_age_group_8_to_14_Days, milestone_age_group__15_to_30_Days, milestone_age_group_31_Days_or_More), 'Overdue', '0-7 Days', '8-14 Days', '15-30 Days', '31+ Days') as milestone_age_bucket,

I'm getting an error that says it can't find the milestone_age_group fields now.