3 Replies Latest reply: Jan 26, 2015 8:09 AM by Jonathan Dienst RSS

    Line Chart Unfiltered Dimension

      Dear Community,

       

      I am trying to have a fixed x-axis (time) and I would like to show only the data that is selected in the graph but not change the x-axis. At the same time I want to be able to select a date on the axis.

       

      This is done by:

      Sum({1}IF(Match(YEAR([ChartDateTime]), $(=GetFieldSelections([Jahr])) ),[PO value (custom)]))

      and works fine.

       

      However, I also want to show all data if nothing is selected.

      I tried:

       

      If(GetSelectedCount([Jahr])>0,

           Sum({1}IF(Match(YEAR([ChartDateTime]), $(=GetFieldSelections([Jahr])) ),[PO value (custom)])),

           Sum({1}[PO value (custom)])

      )

       

      But for some reason it doesn't work.

       

      Any suggestions?

        • Re: Line Chart Unfiltered Dimension
          Jonathan Dienst

          Hi

           

          GetFieldSelections returns null if nothing is selected, the $(=...) returns an empty string, so you have a syntax error. The expression works if something is selected, because then there is no syntax error.

           

          You could try something like

           

          If(GetSelectedCount([Jahr])>0,

               Sum({1}IF(Index(YEAR([ChartDateTime]), '$(=Concat(DISTINCT ([Jahr])))'), [PO value (custom)])),

               Sum({1}[PO value (custom)])

          )

           

          HTH

          Jonathan

            • Re: Line Chart Unfiltered Dimension

              Thank you, Jonathan!

              Do you know if there might be a way to improve the performance of this task?

                • Re: Line Chart Unfiltered Dimension
                  Jonathan Dienst

                  It's pretty hard to make specific suggestions without sight of your model and data.

                   

                  Some general points to consider:

                  • Be aware that QV appears not to optimise or shortcut expressions, so both legs of the if clause may be recalculated every time you make selections
                  • Sum(If()) statements perform poorly on large data sets. Consider refactoring the expressions using set analysis (you would need a Year field for example)
                  • If the model structure means that the fields in these expressions come from different tables, consider flattening your table structure

                   

                  HTH

                  Jonathan