3 Replies Latest reply on Sep 20, 2018 10:08 AM by Tushar More

    Why is show missing values not showing values & how do I show missing values for a dynamic date range?

    Brett Boresow

      Hello,

       

      I have a dashboard view where my goal is to display results on a rolling 7 day basis with the option to filter by client. I have recreated my issue using the Superstore data set.

       

      I have shipping dates across the columns and three measures (Quantity, Sales, Profit) going across the rows. When the filter is set to "All" the results display correctly because all 7 previous days contain data. When I start to filter by specific states I start running into issues.

       

      ALL STATES

       

      California shows all 7 days as well and correctly shows September 16th because it happens to fall in between the minimum 7 days which has data and the maximum 7 days which has data

      CALIFORNIA

       

       

      Arizona does NOT show the data correctly. It is only showing data for September 13th and not showing the entire date range. This is due to it not having data on the minimum date (September 12th) and the maximum date (September 18th).

      ARIZONA

       

      In my actual data set I have tried a variety of solutions & none have worked. I have a date dimension table which I have tried Right Joining and Full Outer Joining to.

      I have tried Analysis > Table Layout > Show Empty Columns (This shows all columns and ignores my Is Last 7 Days filter)

      I have tried adding ZN calculation

      I have tried Lookup calculation

      I have tried various IFNULL combinations

       

      I have attached the example used above. The calculation for the Dynamic 7 Days is: IF DATEDIFF('day',[Calendar Date],TODAY()-1) >= 0 AND DATEDIFF('day',[Calendar Date],TODAY()) <= 7 THEN 1 ELSE 0 END

      At this point I'm out of ideas and any help would be greatly appreciated!