Tableau report count total number of articles based on article's publish and unpublish date, filter by parameter control date range

Question

I am newbie to Tableau reporting and need your help to enlighten me on Tableau. I am looking for a way to count total number of articles based on article's publish and unpublish date, filter by parameter control date range.

I have provided the sample data and sample report for the challenge I am facing. Basically every article will have publish and unpublish date, I need to count total number of article for each day, based on the date range user select from parameter control. E.g. If user choose to show article number from 1st Jun to 5th Jun, report X-axis will only from date from 1st Jun to 5th Jun.

I have tried to use calculated field, but seems only can calculate for a day.

2 challenges I see:-

1) How to loop through date range for parameter control

2) How to aggregate total article each day based on publish data and unpublish date

Sample data:-

[ArticleNo][Publish Date][Unpublish Date]
 110        2014-May-01   2014-Jun-03
 111        2014-Jun-02   2014-Jun-03
 112        2014-Jun-03   2014-Jun-30

Sample report format, bar/line graph:-

Report date range : 2014-Jun-1 to 2014-Jun-5 (Parameter Control)

Number of Article Published 
   ^
 4 | 
 3 |                     X 
 2 |             X       X
 1 |     X       X       X       X       X
 ---------------------------------------------->
   |   1-Jun   2-Jun   3-Jun   4-Jun   5-Jun
                 Report Date Range

I tried to use the following calculated field, but seems can only calculate for a day:- My calculated field

IF ([Date From] >= [Publish Date] and [Unpublish Date] <= [Date To]) THEN 
      DATEDIFF('day',[Date From],[Unpublish Date]) + 1 
ELSEIF ([Date From] >= [Publish Date] and [Unpublish Date] >= [Date To]) THEN 
      DATEDIFF('day',[Date From],[Date To]) + 1 
ELSEIF ([Date From] <= [Publish Date] and [Unpublish Date] <= [Date To]) THEN 
      DATEDIFF('day',[Publish Date],[Unpublish Date]) + 1 
ELSEIF ([Date From] <= [Publish Date] and [Unpublish Date] >= [Date To]) THEN 
      DATEDIFF('day',[Publish Date],[Date To]) + 1 
END

Thanks for your help first. Cheers.

please post it in your question - comments are not useful for this — NirMH

Alex Blakemore Alex Blakemore · Accepted Answer · 2014-07-16T16:21:15

One solution is to first reshape your data to make the reporting easier, possibly using a custom SQL data connection or data blending or pre-processing.

The custom SQL approach can use a UNION ALL clause to combine a table with itself; data blending can do similar tricks on the client side by having two similar copies of the connection.

Here is a long but good thread discussing a similar, but not identical, problem set. http://community.tableausoftware.com/thread/120614

Hope this gets you started - no time to write more detail right now.

Tableau report count total number of articles based on article's publish and unpublish date, filter by parameter control date range

1 Answers