6 Replies Latest reply on Dec 4, 2018 8:35 AM by tanvi khanna

    Not able to limit view by number of records <help needed>

    tanvi khanna

      Hi all,


      I have a question regarding filtering based on count of total records after applying filters. Since I cant share the actual data and dashboard I have created a dummy data and dashboard for reference.

      In attached workbook: filters: age, country

      total records: 22

      view: % of sum of weight of records by frequency and wave (for reference I have labelled the %sum of weight + count of weight on the bar chart)

      What is needed - after filtering on age and country, the count of records reaches a threshold (say less than 5)  - for this sample size the view should not be available but should be seen when total count of records in view > 5

      What I have tried - as you might see in the workbook I created a filter applicable to the other worksheet, count of records >5. This is filtering out every bar in the view whose count of record <5, however I need it to filter only when the total records < 5. To illustrate, in the below image the count of records (last column) for each row is less than 5 except the third last row, hence after applying the filter I made it gives only that particular row. Whereas the actual result should give me everything since total records are 22 which is greater than 5. please find workbook attached.


      I want to limit views by sample size of view (total records in the view) not count of records in each row.


        • 1. Re: Not able to limit view by number of records <help needed>
          Jennifer VonHagel

          Hi Tanvi,


          Try this calculation: WINDOW_SUM(SUM([Number of Records])) > 5.


          Set it to True as a Filter and be sure it is computing using Table Down.


          You can see what here that the window_sum will return the total number of records in the view:




          • 2. Re: Not able to limit view by number of records <help needed>
            tanvi khanna

            Thank you Jennifer! This did not strike me, it was very helpful.

            Additionally, I wanted to leave a remark/comment/text/warning instead of blank chart.

            so when all records < threshold the entire chart disappears, I need a comment on the dashboard for every chart that disappears saying that "chart is not visible as selections do not meet x records"

            • 3. Re: Not able to limit view by number of records <help needed>
              Jennifer VonHagel

              Hi Tanvi, you're welcome!


              I actually have a workbook that shows a message when another sheet disappears.  You can download it here and check it out. Let me know if you have any trouble figuring out how it works. 






              • 4. Re: Not able to limit view by number of records <help needed>
                tanvi khanna

                Hi Jennifer,


                Thank you for the help. I understand that the table and the text box were floating objects and text box was ordered to back so that when the table is visible its not seen. However, I am using containers for all my charts since the dashboard is made on automatic device layout for desktop/tablet.

                I have 14-15 charts, each in a container and I am limiting each of them separately by the count of records for that particular chart. so when one chart disappears due to count of records, the container should switch to text - like you have in the workbook.

                • Not sure if I would need to make a text box for each of 14 charts in that case, cause some may appear some might not depending on filters?
                • containers cannot be send to back like floating objects but I know we can restrict its appearance it with a flag. I will have to try that but not sure about creating 14 texts saying the same thing.
                • I cannot share the actual dashboard but I created a dummy layout of what it looks like and each container is coloured. each of the coloured boxes have the charts and could have the texts behind when charts disappear.

                • 5. Re: Not able to limit view by number of records <help needed>
                  Jennifer VonHagel

                  Hmm, yeah floating objects would be a nightmare on automatic layout.


                  This is going to be a lot of work to set up any way you go .  Here are three more options - I think Chart2 will be the simplest.  For these to work, I switched from using a window_sum() Table Calculation to using a FIXED calculation to define my filter. Since I'm using the FIXED calculation, the date quick filter needs to be added to context.


                  You'll find all my calculations in the folders shown below.

                  In this sample data set, I am testing that the total Sales across SubCategories are at least $100,000. If not, the chart should disappear. If you make the date filter choose a small enough date range, there will be fewer than $100k Sales and the charts will disappear.

                  1. Chart 1a is the most complicated, and I doubt it will work for every chart type table you have. You can see it here, but it's probably not the best option.

                  2. Chart 1b uses the Sales >= 100k condition to make the SubCategory labels and Bars disappear, while leaving the view itself intact. This allows the Total Sales in the Title and Error Message in the Title to appear.

                  3. Chart 3 uses 3 objects: a Text Box for the Title, a regular bar chart for the bar chart with nothing fancy done to it (the previous two examples have special calculations for SubCategory and Sales and the Error Message so they can appear and disappear within the view), and a simple view with the Error Message Text.  This uses the condition to make either the entire Bar Chart appear and Message disappear, or the Entire Message view appear and the entire Bar Chart disappear (google Swap and Pop and you're not familiar with this technique).


                  In example 3, if you want the title to dynamically show the Total Number of sales (or anything else dynamic), I'd create yet another view just for the title and use it in place of the Text Box. This would always show and not disappear based on the >= $100k condition.


                  It's a lot of work if you have 14-15 charts to handle. I've done crazier things to get dashboards just so .  Perhaps someone else knows a more streamlined way to do this.




                  • 6. Re: Not able to limit view by number of records <help needed>
                    tanvi khanna

                    Sure Jennifer. Thank you. I will try this and let you know if this helps or if I find a more better way since there are 15 charts.