Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Want to see what's currently in public preview? We've pulled it all together in one place. Check it out here
cancel
Showing results for 
Search instead for 
Did you mean: 
Rehan
Creator III
Creator III

Cretaing MOnth Buckets from Date Field

I have a Date field in the script and I would like to create month buckets like, 

If the date is Less than 30 days  from today , then 1 month,

if the date is less than  60 days old from today, Then 2 months

if the date is less than  90 days old, from today Then 3months,

anything less than 2 years old  from today , '<2 Years old'

 

My Script is as below but It is still giving me some dates not assigned to any buckets.

What am I missing here??

 

If([Last Reload Date] = Date(Today()),'Current',
If([Last Reload Date] > Addmonths(Date(Today()),-1) and [Last Reload Date] < Date(Today()),'<1 Month',
If([Last Reload Date] <= Addmonths(Date(Today()),-1) and [Last Reload Date] > Addmonths(Date(Today()),-2),'1 Month',
If([Last Reload Date]<= Addmonths(Date(Today()),-2) and [Last Reload Date] > Addmonths(Date(Today()),-3),'2 Months',
If([Last Reload Date] <= Addmonths(Date(Today()),-3) and [Last Reload Date] > Addmonths(Date(Today()),-4),'3 Months',
If([Last Reload Date] <= Addmonths(Date(Today()),-6) and [Last Reload Date] > Addmonths(Date(Today()),-7),'6 Months',
If([Last Reload Date] <= Addmonths(Date(Today()),-9) and [Last Reload Date] > Addmonths(Date(Today()),-10),'9 Months',
If([Last Reload Date] <= Addmonths(Date(Today()),-12) and [Last Reload Date] > Addmonths(Date(Today()),-13),'12 Months',
If([Last Reload Date] <= Addmonths(Date(Today()),-15) and [Last Reload Date] > Addmonths(Date(Today()),-16) ,'15 Months',
If([Last Reload Date] <= Addmonths(Date(Today()),-18) and [Last Reload Date] > Addmonths(Date(Today()),-19),'18 Months',
If([Last Reload Date] <= Addmonths(Date(Today()),-21) and [Last Reload Date] > Addmonths(Date(Today()),-22),'21 Months',
If([Last Reload Date] <= Addmonths(Date(Today()),-24) and [Last Reload Date] > Addmonths(Date(Today()),-25),'24 Months',
If([Last Reload Date] <= Addmonths(Date(Today()),-25), '> 2 Years Old'
)))))))))))))) as [App Reload Age]

 

Labels (1)
1 Solution

Accepted Solutions
Rehan
Creator III
Creator III
Author

FOund the missing months

View solution in original post

3 Replies
MarcoWedel
MVP
MVP

There are buckets missing.
e.g. what bucket would you expect an 8 months old date to be assigned to?

Rehan
Creator III
Creator III
Author

I am trying to figure out which buckets are missing, how do I fix that . I want the  above mentioned buckets

Rehan
Creator III
Creator III
Author

FOund the missing months