11 Replies Latest reply on Nov 28, 2018 8:18 AM by Joe Oppelt

    Current vs Previous Period

    Prakash Desai

      Hello,

       

      Need some help on the following scenarios/issues.

      1. Always show the previous sub-cat which are displayed in current Sub-Cat only without splitting the sheets for each Sub-Cat. Also if there are no values in previous then the field should display with 0 values. Can we do this dynamically in Tableau without altering the data in the DB?

      Ex:

      Output==>  

       

      2. Other case is even though there are no values in the DB for some of the Sub-Categories, can we display them without splitting(single sheet for each Sub Cat)) the sheet for each sub-category?. Basically wanted to display data for all 8 Sub-Cat and which ever don't have data , measure value should be 0.

      Output==>

       

      3 For those Sub-Cat which do not have any measure values should display as 0 by default for most recent time periods, but whereas for the oldest time period the value should display as "NA".

      Ex: The oldest time period is 2016-H1 and there will be no data for 2015-H2 and for those measure which are displayed should be "NA" dynamically and display the same as the time period passes.

      'Attached workbook in 10.5.

       

      Thanks!

        • 1. Re: Current vs Previous Period
          Joe Oppelt

          In the attached start with Sheet 4.

           

          When you have a dimensional grid, you'll get the cells you need, both forward looking and backward looking.  But you'll still get nulls where there is no data.

           

          See Sheet 4(2).

           

          Here I do a LOOKUP,0 to force data where none exists.  This will work for you in a crosstab format as we have here, but might not work for you in other viz formats.


          See Sheet 4(3).

           

          Here I made my own dimension called [Period] and based it off a parameter rather than a filter.  Doing this puts it all in one grid, and it forces the empty spaces, and it lets you force zeros where no data exists.

           

          Trying to do it with multiple sheets can get really tricky, so if you can get away with doing it in one sheet like this, you'll have a whole lot more success.

          • 2. Re: Current vs Previous Period
            Joe Oppelt

            I see that I didn't address the N/A part.


            The problem you are encountering is that you are looking for Tableau to behave like excel.  A cell doesn't exist in Tableau if there is no data there.  You need something to force Tableau to consider its existence.  (Which is why the crosstab handles the LOOKUP for us in Sheet 4(2), but other vizzes wouldn't.  The combination of the column and row dimensions builds that grid.)   Excel cranks out a grid first, and then you insert data into it.

             

            The complete absence of a "Previous" column for 2016-H1 means there is nothing to stretch Tableau's visualization to the "Previous" column.  Nothing is there, so nothing gets displayed.  If you had even one row in your data with a Date value of 2015-H2, even if just null values for all the other fields, you would get the grid to show that column.  (And we'd have to expand the [Prior Date] calc accordingly, of course.)

             

            And then there is the issue of pushing out a string value of "N/A" instead of zero.  We can do that if needed.

            • 3. Re: Current vs Previous Period
              Prakash Desai

              First I would appreciate Joe for looking into my use cases. I think the approach is very close with Sheet4(3) solution out of other sheets, but had few concerns..

               

              1. Since we go with parameter, the values will not update based on the refresh. We need to manually edit the Param values.

               

              2. Total we have 8 Sub-Cat from the data set, but we display those only available for that filter criteria which is the tool  behavior. Is there a way to show them all 8 in the sheet & make the measures values as 0 for those which don't have data for all time periods except lowest time period i.e. NA should be displayed for 2016-H1. Later after few months if 2016-H2 is the lowest time period then it should display NA and so on as new time period comes in.

              Sub-Cat list will be constant/freeze.

               

              3.  If there is no data for 2017-H1 in the data set, then need to show them as 0 values.

              Output==>

               

              Appreciated it..

              Thanks!

              • 4. Re: Current vs Previous Period
                Joe Oppelt

                 

                 

                First I would appreciate Joe for looking into my use cases. I think the approach is very close with Sheet4(3) solution out of other sheets, but had few concerns..

                 

                 

                Right.  The first two sheets were just a progression of steps to show why we need Sheet 3.

                 

                 

                 

                1. Since we go with parameter, the values will not update based on the refresh. We need to manually edit the Param values.

                 

                 

                 

                Tableau version 2018.3 has a new feature called set actions which can build a parameter that updates as the data source updates.  Before that version, yes, you would need manual maintenance of the parameter list.

                 

                 

                 

                2.  Is there a way to show them all 8 in the sheet & make the measures values as 0 for those which don't have data for all time periods

                 

                 

                As I mentioned in I previous reply, Tableau will only show stuff where there is actual data to show.  If either sub-category has a row, then tableau will make that row in the grid.  If a sub-category (or a period value) is totally missing, then that row or column will not appear at all.

                 

                You can force every time period and sub-category to appear by building a data scaffold.  Google for "tableau data scaffolding" to get a tom of discussions about it.  Here is a very good one:

                 

                Vizible Difference: Data Scaffolding in Tableau

                 

                 

                If you do this, you'll be able to fill in periods that are missing entirely, and force all sub-categories to appear all the time, and more easily deal with that last period to display N/A.

                • 5. Re: Current vs Previous Period
                  Prakash Desai

                  Hi Joe,

                   

                  I have gone through some Data Scaffold mappings and came up with a table structure, but had some issues in displaying the data in the actual grid.

                  Sheet5: i.e.Measure values are not changing for each time period and few of the Sub-Cat values are getting excluded when excluded nulls in filters.

                  Also when i pull the Dim from(Period) from secondary DS then i get an "cannot blend secondary data source because one or more fields use an unsupported aggregation" error. Please advise.

                  • 6. Re: Current vs Previous Period
                    Joe Oppelt

                    Some things I see:

                     

                    1:  You need to blend on [Date] in addition to [sub-category].  (That's why the same numbers are appearing in all the dates right now.)

                     

                    2:  Doing COUNTD on the secondary data source won't work.  It is working in this simplest of examples, but you'll find that if you add much of anything to that sheet (including the additional blend field) you'll get a red pill for [Latest] or [Latest 2].  Of course, as your sample data is set up right now, COUNTD is the only thing that makes sense since you are just counting Customer IDs from the secondary data source and there are some duplicates in some category/date cells.  But more than likely you'll be counting invoices or sums of sales or something that won't require a COUNTD.

                     

                    2A:  For the sake of the demonstration of a scaffold here, I changed [Latest] in the clipboard data to do COUNT.  Not COUNTD.

                     

                    3:  The LOOKUP (or any table calc) should be done in the primary data source, not the secondary data source.  (Just a good practice, in my experience.)  But the reason you are getting inflated numbers is because the table calc is adding up all the numbers across the row.  You just want to look up what's in the specific cell right now.  On your sheet 5 I did my own LOOKUP in the primary data source rather than in the secondary.  But for demonstration purposes, I have displayed both.  For each one I changed the table calc settings to "Cell" so that it just looks up in its own place and not across the whole row.  All the values, no matter how I draw the data (whether using [Latest] or LOOKUP, etc.) show the same number.  And yet there are still all the blanks in there!

                     

                    4:  Your scaffold needs to encompass every possible row/column combination.  I dragged your data into my own excel file (see attached) and made rows for all those combinations at the bottom of the file.  (I didn't bother with Country or Region or State, but if you have to have them represented on your sheet as well, then you'll need a multiplication of all those dimensions so that every possible combination is covered in the scaffold.)

                     

                    Look at sheet 7.  I used the new data source, and I made two calcs.  the first pulls the count from the clipboard data source.  The second does a ZN() of the first calc.  ZN shoves in a zero where there is currently a null.  (If you swap the current displayed measure with the [Count from clipboard] calc, you'll see where the nulls are.)

                     

                    Because of the shape of the data in the new scaffold, every cell has a value.

                     

                    Go to sheet 7(2).  Here I can filter out Null from the [Period] values, and the remaining full grid has all the sub categories, even when there is no data in the underlying secondary source.  That is what the scaffold does for you.

                    • 7. Re: Current vs Previous Period
                      Prakash Desai

                      Thanks Joe.

                       

                      I think we are almost there.. however had one issue when pulling other metrics in the grid. Please let me know if there is any other work around or this approach will not work apart from DB changes.

                       

                      In New sheet: I tried creating a Text Tables by pulling few measures, but not aligning as expected. But i wanted to display first 3 columns for selected time period & 4th column would be of previous time period diff.

                       

                      Expected output.

                       

                      Appreciate it.

                      • 8. Re: Current vs Previous Period
                        Joe Oppelt

                        As I mentioned before, using COUNTD in the secondary source isn't going to work.

                         

                        I know this is a sample workbook.  Will you actually need COUNTD in your actual application?

                         

                        (That's not the issue you are encountering in your current workbook, BTW.  I'll be doing some stuff with it to get you going.  But if you actually need to do COUNTD, we're going to have to look at doing something different altogether.)

                        • 9. Re: Current vs Previous Period
                          Prakash Desai

                          Thank You once again.

                           

                          Yes, we use COUNTD in most of our calculated fields in our application.

                          • 10. Re: Current vs Previous Period
                            Joe Oppelt

                            I have a bunch of explaining to do...

                             

                            When you have a blended secondary data source, Tableau does not do row-level evaluation of the secondary data.  It only does aggregate-level evaluation.  (This is why COUNTD won't work.  COUNTD requires row-level evaluation to do the distinct counting.)

                             

                            Likewise, the evaluation of [Status] isn't going to happen at the [Period] level.  You have visibility into the secondary source for [Period] and for [Sub-category] because they are blend fields.


                            Also, you want to do all your math calcs in the primary data source.  I made a copy of your NEW sheet.  Notice how I brought the data from the secondary source to the primary (see [Count N] and [Count Y].)  And how I'm doing the math in the primary data source.  (See [DIFF current] and [DIFF prior].)

                             

                            In the attached I was trying out a ton of things.  First I want to point out that the dimensionality of sub-category with Period forces a multi-line shape to your data.  Even if I move [Period] to Rows (as I did on your NEW sheet and my copy of it), you get multiple rows per sub-category.  That's what you were seeing in your original setup as well.  You might need to break out multiple measures in your secondary source.  See [Count Y for Prior] in the clipboard source.  Now SUM([Clipboard].[Count Y for Prior]) in your primary source will be just the prior count.

                             

                            The more that I play with this, the more I wish you just had all the dimensions filled in one data source rather than trying to build the scaffold.  You could either create that in the original data source at its creation, or use Data Prep to UNION these two data sources into one, and use the output of that.

                             

                            But for now, see attached.

                            • 11. Re: Current vs Previous Period
                              Joe Oppelt

                              Prakash Desai wrote:

                               

                              Thank You once again.

                               

                              Yes, we use COUNTD in most of our calculated fields in our application.

                              That's all the more reason to use Data Prep or some other means to create a single data source here.

                              • 12. Re: Current vs Previous Period
                                Joe Oppelt

                                Prakash -- Just came across this link in another thread.  Might be worth checking out.

                                 

                                Alternatives to Count Distinct: Tips for Faster Calculations