6 Replies Latest reply: Aug 9, 2017 12:37 PM by Marcus Sommer RSS

    AD HOC Reporting in Qlikview

    Ashley Navin

      We are using the AD HOC reporting capability in Qlikview and it is working well with conditional measures and dimensions. The issue we are having is that the column on the chart appear completely arbitrarily - not in the way that they were selected. This requires the user to then export it to excel and re-arrange the columns in the order they want. Is there any way to make the columns the way the user selected them?

       

      Thanks

        • Re: AD HOC Reporting in Qlikview
          Sunny Talwar

          AFAIK I don't think you will be able to sort the columns based on user selection.

            • Re: AD HOC Reporting in Qlikview
              Ashley Navin

              Is there a way to sort at all? Right now it is totally random so we cannot even predict where the different columns will appear.

                • Re: AD HOC Reporting in Qlikview
                  Sunny Talwar

                  Is this happening with Dimension or Measure? What are the exact expression you are using for your calculated dimension and your expression? If there is a Concat somewhere in your expressions, I would expect it to be sorted in ascending order of your field name... (unless you have a sort in your concat)... but there are a lot of things and I am not sure what you have. May be a sample might be helpful here

                    • Re: AD HOC Reporting in Qlikview
                      Ashley Navin

                      It is happening with both the dimensions and measures.

                      =SubStringCount('|' & Concat(distinct Dimension, '|') & '|', '|Account Number|')

                      =SubStringCount('|' & Concat(distinct Measure, '|') & '|', '|Account Name|')

                       

                      I have a sort on the list box of the measures but that is not at all related to the table. Is it the way i have the measures listed in the script? See below:

                       

                      Measures:

                      LOAD

                      *

                      INLINE [

                      Measure

                      Book Balance

                      Days Delinquent

                      Name Line 1

                      Customer Balance

                      GL Balance

                      Risk Amount

                      Maturity Date

                      Facility Grade

                      Facility Rating

                      Borrower Grade

                      TDR Flag

                      Non-Accrual Flag

                      Note Date

                      Current Note Date

                      Specific Reserve

                      YTD Interest Paid

                      Delinquency Stage

                      Loan Status

                      Branch

                      Appraisal Amount

                      Appraisal Date

                      Times 30 Days Past Due

                      ];

                • Re: AD HOC Reporting in Qlikview
                  Marcus Sommer

                  Sunny is right - it's not directly possible. At least not without more or less efforts and the loss of usability. Therefore isn't it a possibility that the user after he/she has choosen their dimensions/expressions draged the columns on the position which is wanted?

                   

                  One possibility (with some work) to solve this challenge could be to create at least one variable for each possible dimension and to use quite heavy expressions within them with a lot of if-loops and replace-statements to check the other variables and to remove all already existing dimensions from the most recent concat of the possible dimensions. Probably far more complicated would it be to find a stable solution for the case a user deselect some of these dimensions again - a rather simple workaround to this might be to use another button which cleared all variables and set in them the origin expressions again. In general it should be possible but it won't be very funny else rather frustrating.

                   

                  Another possibility which we have implemented is to use a table-chart (as a kind of multibox: http://www.qlikfix.com/2014/07/08/building-nicer-dynamic-multibox/) for the selected dimensions and to load the dimension-list several times into different dimension-fields (you could also use several listboxes for them). But the user will need to use a dropdown to select each wanted dimension.

                   

                   

                  and within the chart it will be referred in this way:

                   

                   

                  We have restricted this kind of usability to 3 vertically dimensions and 2 horizontally dimensions (is a pivot and dragging of the dimensions is disabled) and we don't the apply the same logic for measures because the object is mainly for adhoc-analysis and not for reporting which is probably the reason behind the wish to get always the same structures - and in our case it worked well enough.

                   

                  - Marcus