This is doable, but need a workbook
The first question is do you have data that you expect in each of the buckets?
that is is the calculation working it is just that data that results in nothing with the label you are missing?
Check each clause individually if you need to
If the formula is producing the result consistent with the data then
if that doesn't work please attach your twbx workbook
Try this and let us know if this work. Else kindly share some sample mock up data in packages workbook as suggest earlier by other members.
IF (DATEDIFF('day',[some Date],TODAY()) <= 1 THEN "0-1 Days"
ELSEIF DATEDIFF('day',[some Date],TODAY()) <= 7 THEN "2-7 Days"
ELSEIF DATEDIFF('day',[some Date],TODAY()) <= 14 THEN "8-14 Days"
ELSEIF DATEDIFF('day',[some Date],TODAY()) <= 28 THEN "15-28 Days"
ELSE "29+ Days"
If the column [some date] has no date then your calculation will not be evaluated. If you put your entire IF statement inside a IFNULL() you can set null values to “0-1 Days”.
IFNULL( IF...then...elseif...elseif...END, “0-1 Days”)
Thank you for replying. I am attaching workbook. where i have calculated field bucket and bucket list. both have the same calculation as it is not showing the bucket list. I don't have any data for these list, but still i have to show them in the bar chart. My requirement is to show top 5 usernames and remaining usernames as others in the same as stacked bar chart falling under this bucket. Even it has no data it should show on the chart as data will update.
workbook.twbx 54.8 KB
I looked at the data you provided. Your 'Bucket' group is calculated versus MAX(Ingestion date) and does not produce values in each of the bucket groupings you listed. So the bar chart you showed is correct. It will not show 1-7 days because there are no rows of data for that group.
Your 'Bucket list' calculation is calculated versus Today() and all the values for each row are over 100 days, thus everything will fall into one bucket.
But my requirement is to show that empty columns (bucket list) on the chart. It is not coming on the chart to display.
1 of 1 people found this helpful
There is another message string asking this same issue, same data. If statement with multiple conditions
The issue you have is that your data set has no rows of data that evaluate to “2-7 days”. Your IF statement doesn’t create new row of data, it only adds a value to the rows you have. To get a zero value in the “2-7 Days” group you have to force your data set to add a row that has that value.
If you look at the other message string I explained how to do it. It is not elegant but it works.