8 Replies Latest reply on Jun 19, 2018 12:02 PM by Orlando Suarez

    Help with Calc that will eliminate rows without current quarter values in a dual axis trend line

    Orlando Suarez

      I know this is probably not best title for this request but I'm unsure of how to describe what we are looking for but here goes.

       

      I have a worksheet where I'm making sparklines (trend lines for each model of car for a given make) I use a Sort by calculation that I call "Sort + Rank (MAKE MODEL) Last" this calculation tells the view to sort the model/trend lines in descending order based on the most current quarter (for this example the most current quarter is Q1 2018). I've posted the calculation in the view below. I want to be able to modify that calculation so that it not only Sorts and Ranks based on the most current quarter....but it will also eliminate and Models/rows that don't have current quarter data or values.

       

      In the example below the first 3 Mercedes-Benz models (B-Class, G-Class and SLC) do not have Q1 2018 performance or values....I'd want those to drop off. Everything else stays because they do have Q1 2018 performance or values.

       

      So how can I do this?

       

      Sorry, I'd attach a workbook if I could but this is protected info...so trying to supply as much as I can on the screen shot.

      Eliminate rows with no current quarter performance.png

        • 1. Re: Help with Calc that will eliminate rows without current quarter values in a dual axis trend line
          Okechukwu Ossai

          Hi Orlando,

           

          An alternative approach will be to use a separate filter. I don't know the fields in your database. But assuming you have a field called [Current Quarter] then use this calculation ISNULL([Current Quarter]) . Place the field on the filter shelf and set to 'False'.

           

          This will depend on your data structure. Alternatively, if you have a field called [Quarter] and another field, called [Current Quarter]. Then you can try something along this line;

          IF {Fixed [Model Name]: MAX([Quarter])} = [Current Quarter] THEN 'Show' ELSE 'Hide' END

          Use this as a filter and set it to 'Show'. Remember all the formulas here are pseudo codes. You will need to modify them based on your dataset.

           

          Hope this helps.

          Ossai

          • 2. Re: Help with Calc that will eliminate rows without current quarter values in a dual axis trend line
            Sohan Jawar

            Hi,

             

            Add Sort+Rank field to the filter as a Measure filter Not NULL Values.

             

            Change the same field to Attribute and again select No NULL Values.

             

            Regards

            Sohan.

            • 3. Re: Help with Calc that will eliminate rows without current quarter values in a dual axis trend line
              Orlando Suarez

              Thanks Okechukwu Ossai for your suggestion. It appears that Sohan Jawar solution worked the best for this specific application.

               

              Sohan I just dragged and dropped the "Sort + Rank" pill into the filters shelf and changed it to continuous and it worked automatically. I didn't have to do anything further. So thank you for providing this option. I really do appreciate all your help!

               

              Orlando

              • 4. Re: Help with Calc that will eliminate rows without current quarter values in a dual axis trend line
                Orlando Suarez

                Hi Sohan Jawar . So I'm having to revisit the issue described above because I'm running into a couple of problems.

                 

                Sohan , when we last connected I dragged my "Sort + Rank" field to the filters shelf and I converted it to continuous. This did what it was intended to do....which was to eliminate any rows from the rank that had NO values for the most current quarter. Thus the reason I responded on this post saying it worked.

                 

                However my "Sort by" parameter which is powered by the "Sort + Rank" field also has Q/Q, Y/Y and Model sort options so the user can sort the dashboard view and see each row sorted by any of those requests. So here is when the problem comes in. Say for example a user selects Y/Y in the "Sort By" parameter and some of the rows have some NULL values for the previous YEAR/OURTER...those rows disappear. When actually I don't want them to. Same is true for Q/Q. Oh, and by the way....if the user selects "Model" on the "Sort By" Parameter....everything disappears all together.

                 

                So I need to be able to make an adjustment where the "Sort+Rank" field still eliminates any rows that don't have any values for the most current quarter....but will leave Q/Q, Y/Y and Model options untouched so the user can sort those as they please.

                 

                I tried going back to your original post where you suggested I make the "Sort + Rank" field and attribute. But when I throw the "Sort + Rank" field onto the filters shelf I don't get an "ATTRIBUTE" option available to me. So unsure how to do that and it that will fix the problems I just described.

                 

                I'm going to attach some visuals below to show what I'm running up against:

                Trendline Issue 6-18-18.png

                • 5. Re: Help with Calc that will eliminate rows without current quarter values in a dual axis trend line
                  Sohan Jawar

                  Hi Orlando,

                   

                  When there are no values or nulls for the Sort+Rank field only then the values will get filtered. However once filtered you would not be able to see these values. I am not sure why, when the values are nulls you want them to be displayed and sorted?

                   

                  This functionality may not be possible to ensure filtering as well as displaying the values when needed in the same view.

                   

                  However you can try one thing use a calculation for the Parameter SortBy:

                   

                  Cal = (ATTR([Parameter Value]) = "Q/Q" AND ISNULL([Sort+Rank])) OR (ATTR([Parameter Value] = "Y/Y" AND ISNULL([Sort+Rank])).....etc. for all the parameter values, drag it to filter (instead of the [Sort+Rank] field) and select NULL as well as TRUE.

                   

                  Regards

                  Sohan.

                  • 6. Re: Help with Calc that will eliminate rows without current quarter values in a dual axis trend line
                    Orlando Suarez

                    Sohan,

                     

                    Thanks for checking back in on this.

                     

                    So in essence what I want is for the calculation (whether that be the Sort + Rank calc that powers the Sort By parameter or any additional/new calculation that we create)  to:

                    • Eliminate any rows that have a NULL or no value ON JUST the most current quarter (in my example that would be Q1 2018)
                    • However that same calculation should not remove ANY OTHER rows (like rows that have NULLS in Q/Q or Y/Y) that show NULL or no value on ANY OTHER quarter/year combination that is not current quarter
                    • It should also allow the user to still sort the rows by the MODEL dimension name

                     

                    I tried constructing the calculation you referenced in your last reply but I keep getting an error at the end of the calc:

                    Custom Calc.png

                    • 7. Re: Help with Calc that will eliminate rows without current quarter values in a dual axis trend line
                      Sohan Jawar

                      Hi Orlando,

                       

                      As you would like to eliminate rows only for the current quarter can you try the below calculation.

                       

                      Create a calculated field for year & quarter combined: [QY] = 'Q'+STR(DATEPART('quarter')) + '-' + STR(YEAR([Date]))

                       

                      Create another calculation with For Sort+Rank:

                       

                      [New Sort+Rank] = IF [QY]<>"Q1-2018" THEN [Sort+Rank] END

                       

                      Now Drag this in the Filter instead of the original Sort+Rank, Select Special(Non Null Values) and check the results.

                       

                      Regards

                      Sohan.

                      • 8. Re: Help with Calc that will eliminate rows without current quarter values in a dual axis trend line
                        Orlando Suarez

                        Sohan Jawar Sadly those options did not work. However I was able to make it work by doing this:

                         

                        1/ Created a calculation that I call Last Loyalty:  LOOKUP(SUM ( [LOYALTY_PERCENTAGE] ), LAST () )

                        2/ I then place that on the ROWS Shelf and EDIT TABLE CALCULATION to Table Across and I make it DISCRETE

                        3/ Using the CONTROL button I drag and drop a copy of that pill from the ROWS Shelf to the FILTERS Shelf. When the FILTERS edit window opens I scan all the values for looking for #MISSING. If a #MISSING option shows up in the mix I click NONE then I click EXCLUDE and place a check mark only next to the #MISSING option.

                        4/ Now I remove the "Last Loyalty" pill from the ROWS shelf all together .

                         

                        This made it possible for the trendlines to still rank according to the most current quarter (Q1 2018) but it also eliminates any rows that NULL values for the most current quarter. This calculation also makes it possible to have the parameter sort all the options properly.

                         

                        Trendlines like this are kind of a pain but thankfully this is one way to get around them.