5 Replies Latest reply on Feb 24, 2016 3:19 PM by Jing Qing

    Pad past nulls to 0, but not future ones?

    Jing Qing

      Hi all,


      I'm trying to create a heat map based on the number of sales in the past 3 weeks. Based on the day of the week the report is generated, the heat map may contain dates that are in the future. I would like to keep to keep the cells for the future dates "white" (or have null sales).  


      The number of sales is calculated by counting the Customer ID on that day. If there was no customer IDs recorded on a past date, count(Customer ID) would return null value and the cell would show up blank. I tried to convert null values to 0, but it would convert future sales to 0 as well.


      After reading on past missing dates, I created a dummy file containing all past and future dates (1/1/2012-12/31/2016, placeholder data) and used it as the primary data source to blend with the actual data that contains customer IDs. With a filter to limit the dates to be <Today(), I was able to get a crosstab view of the table that I wanted (not sure if this is the correct approach). However, when I tried to add rank to it in order to get the heatmap view, the aggregates sales values changed in the view and I couldn't figure out why.


      The goal is to create a heat map with similar format to the tab "Padded and Filtered", but with past days with 0 sales marked with red background, and future dates with null data as blank (or white background).


      Any insights will be appreciated!

        • 1. Re: Pad past nulls to 0, but not future ones?
          Joe Oppelt


          Change the definition of the rank table calk to incorporate the whole table.  I'm not sure why it's changing the actual values of AGG(Sales), but you certainly do want it to look at the whole table, not look at it row-by-row, as TABLE(ACROSS) will do, or column-by-column as TABLE(DOWN) will do.

          • 2. Re: Pad past nulls to 0, but not future ones?
            Joe Oppelt

            I made this change in the Sales Numbers changed with rank sheet.  Attached.

            Also look at sheet 6.  You don't really need the [Sales] calc in the primary data source to display data from the secondary source.  Just drag the field from the secondary source right onto the sheet.

            • 3. Re: Pad past nulls to 0, but not future ones?
              Jing Qing

              Hi Joe,


              Thanks for the prompt response. In our heat map, we actually do want to restart the ranks by row.  Due to the weekly seasonality, for example, we want to compare the number of sales for the current Thursday to previous Thursdays, not with Mondays or Sundays. If I don't blend with the place holder dates, a calculation field like this worked great for me:


              if not isnull(count([Customer Id]))

              then rank(count([Customer Id]))

              else 0



              This way I could force all null values to be rank 0, but the problem is that it couldn't tell the difference between past dates with actual 0 sales and future dates where the sales haven't happened yet (and thus I went with the data blending).


              I tried to use [Sales] (primary data source) and [Actual Sales] (from secondary data source) in the view, but neither of them worked right under rank across .

              • 4. Re: Pad past nulls to 0, but not future ones?
                Joe Oppelt

                Well I tried something different.


                I went into the advanced editor and specified for the table calc (rank) to run through the two dimensions (weekday of patched date, and day of week start).  And to restart every weekday of patched date.


                That didn't do it.  But then I decided to put the actual [Patched Date] dimension on the data shelf, and then add it into the advanced table calc editor.  And that did it.

                I had messed around with various things.  One was to create a string dimension for weekday of patched date.  (Used in sheet7.)  And that's what prompted me to put the [patched date] dimension on the data shelf.


                Turns out it doesn't matter if you use WEEKDAY(Patched Date) or the string calc I made.  It turns out that putting the actual [Patched Date] dimension on the data shelf was the trick.

                And you'll see that on one sheet I did EXACT DATE on [Patched Date] and on another I just left it as the default YEAR(Patched Date).  Either way works.  I guess when the default YEAR() goes on, it still has the full data value of the field, but when you have the specific WEEKDAY() as it is in the ROWS shelf, it can only see a piece o the value.


                Anyway, now you are getting ranks for the values within a row.

                See attached.

                • 5. Re: Pad past nulls to 0, but not future ones?
                  Jing Qing

                  Wow it worked! I have never thought of putting the [Patched Date] dimension on the data shelf and it did the trick.


                  Thanks again! I'll now play with the actual data and hopefully the data blending will not give me more headaches