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!
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],
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):
