AD HOC Reporting in Qlikview

[Anonymous]

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

[sunny_talwar]

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

[Anonymous]

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.

[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

[Anonymous]

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

];

[sunny_talwar]

So you have pre-listed all your dimensions and expressions and you just enable/disable them based on selection? The order of your dimension and expression will be the order they are in your chart.

[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