9 Replies Latest reply on Dec 19, 2017 3:54 PM by Nicole Le Prohn

Create Range of Days using Date field

Jesse Narang Dec 19, 2017 12:10 PM

Hello, Tableau community,

I have a simple table that looks like below:

I have selected 2 months in the date filter - November and December.

I am trying to create calculated fields that will show me the revenue breakdown at different time intervals i.e I want the table to look like below:

I will be using different month intervals going forward i.e next month I will choose December and Jan, following month I will select Jan and Feb and so on therefore I want the calculated fields to show revenue in the same structure as displayed in the image above which is same time intervals like 1-7 days, 8-14 days, 15-21 days, 22-28 days and then full month revenue but for different months.

Is there any way possible to do this?

I have attached my workbook also

Thanks,

Jesse

Sample Revenue Churn.twbx 11.2 KB

1. Re: Create Range of Days using Date field

Nicole Le Prohn Dec 19, 2017 12:31 PM (in response to Jesse Narang)

Jesse, i'm not seeing a workbook attached...
Like Show 0 Likes(0)

Actions
2. Re: Create Range of Days using Date field

Barry OConnor Dec 19, 2017 12:42 PM (in response to Jesse Narang)

Jesse,

One way to do this would be to create a calculated field like so:
This will create the calculated values you are looking for. To show those values with the monthly values, put them together in a dashboard:
1 of 1 people found this helpful
Like Show 1 Likes(1)

Actions
3. Re: Create Range of Days using Date field

Jesse Narang Dec 19, 2017 1:32 PM (in response to Nicole Le Prohn)
Hi Nicole,

That's weird because I still see the attached file but I have also attached it to this message. please let me know if you are still having issues viewing the file.

Sample Revenue Churn.twbx 11.2 KB
Like Show 0 Likes(0)

Actions
4. Re: Create Range of Days using Date field

Jesse Narang Dec 19, 2017 1:34 PM (in response to Barry OConnor)

Hello Barry,

Thanks for your message. Is it possible for you to include the file also? Thanks!
Like Show 0 Likes(0)

Actions
5. Re: Create Range of Days using Date field

Barry OConnor Dec 19, 2017 1:45 PM (in response to Jesse Narang)
Jesse,

Sure, here is the revised report. But what version of Tableau are you using? I am using 10.3, so if you are using an older version you may not be able to open it.

Sample Revenue Churn Revised.twbx 23.3 KB
Like Show 0 Likes(0)

Actions
6. Re: Create Range of Days using Date field

Nicole Le Prohn Dec 19, 2017 2:04 PM (in response to Jesse Narang)

it showed up after i refreshed. And i came up with the same basic formulas and recommendations as barry. hope it worked for you
Like Show 0 Likes(0)

Actions
7. Re: Create Range of Days using Date field

Jesse Narang Dec 19, 2017 2:35 PM (in response to Barry OConnor)

Hello Barry,

Thanks for sending me the file!. It works great I do have one more question - Is it possible to make the table look like below:

So basically now the time intervals are - Revenue in first 7 days, Revenue in the first 15 days, Revenue in the first 21 days, Revenue in the first 28 days and then full month revenue.

Is that possible?
Like Show 0 Likes(0)

Actions
8. Re: Create Range of Days using Date field

Jesse Narang Dec 19, 2017 2:37 PM (in response to Nicole Le Prohn)

Hello Nicole,

Thanks for your reply but I do have another question and if you or barry could solve it then that'd be great - Is it possible to make the table look like below:

So basically now the time intervals are - Revenue in first 7 days, Revenue in the first 15 days, Revenue in the first 21 days, Revenue in the first 28 days and then full month revenue.

Is that possible?
Like Show 0 Likes(0)

Actions
9. Re: Create Range of Days using Date field

Nicole Le Prohn Dec 19, 2017 3:54 PM (in response to Jesse Narang)
sure. change the formula to look like this so your headers look better:
IF DAY([Date]) < 8 THEN
    'Revenue between ' + str(DATENAME('month',[Date])) + ' 1-7'
ELSEIF DAY([Date]) < 15 THEN
    'Revenue between ' + str(DATENAME('month',[Date])) + ' 1-14'
ELSEIF DAY([Date]) < 22 THEN
    'Revenue between ' + str(DATENAME('month',[Date])) + ' 1-21'
ELSEIF DAY([Date]) < 28 THEN
    'Revenue between ' + str(DATENAME('month',[Date])) + ' 1-28'
ELSE
    'Total Revenue for ' + str(DATENAME('month',[Date]))
END

and then do the revenue as a running sum across, restarting every month.

workbook attached...although in 10.4 so you might not be able to open it. Let me know if you need more help.
Nicole

Sample Revenue Churn Revised.twbx 44.3 KB

1 of 1 people found this helpful
Like Show 1 Likes(1)

Actions