topic How do you toggle between expressions for sorting in a bar chart in QlikView

How do you toggle between expressions for sorting in a bar chart

Wed, 03 Aug 2011 16:44:11 GMT

I have a bar chart with three expressions. One for current year, last year and growth. I have a users who wants to be able to sort by any of these three expressions. Basically have the option to select which expression to sort by. Right now, it seems I can only sort by one expression without an ability to change at the end user level.

How do you toggle between expressions for sorting in a bar chart

johnw — Wed, 03 Aug 2011 19:51:58 GMT

Maybe create a "sort by" field with the labels for your three expressions as values. Then use that to pick the correct sort expression in the chart.

How do you toggle between expressions for sorting in a bar chart

quiquehm — Wed, 29 Feb 2012 23:28:45 GMT

I have a similar problem but I need to toggle ( or enable ) various lines in a line chart based on the selections I make on a field. I explain in detail : I get periodic ( weekly ) data extractions from SAP ( same report with same data fields basically ) with a product sales forecast per month. Concatenating all these extractions I create a calculated field called Week_Nr ( with values 1,2,3...etc, corresponding to the week when the data is extracted ). What I need is to setup a line chart to be able to trend over time the cumulative product sales on the different weeks that I select in the Week_Nr field.

For example I would like to see 2 trend lines ( one corresponding to the data taken on week 7 and a second line showing the data taken on week 8 ) ...this way I could do deviation analysis comparing the forecast on different weeks....similarly I would like to be able to select 3, 4 ..weeks ..or 2 non consecutive weeks for example.

At this moment I managed to display 2 lines using 2 expressions in the chart setup and with Set Analysis ( so the first expression is plotting the data when Week_Nr=7 ( using a set modifier )..and similarly the second expression for Week_Nr=8 ( with a set modifier as well ). So how could I make these set modifiers kind of dynamic or linked to whatever week I manually select in the Week_Nr field ?

Appreciate any help

Thanks in advance

Enrique

How do you toggle between expressions for sorting in a bar chart

Anonymous — Thu, 01 Mar 2012 02:36:58 GMT

I once had a similar requirement, and solved it by creating a macro (assigned to a button) that was changing chart properties.

How do you toggle between expressions for sorting in a bar chart

quiquehm — Thu, 01 Mar 2012 08:48:53 GMT

Michael, could you please give me the macro details to see how to do it. Unfortunatelty I never worked with macros in Qlikview.

Anyone has any other trick without using macros ?

Appreciate your help

Thanks

Enrique

Re: How do you toggle between expressions for sorting in a bar chart

johnw — Thu, 01 Mar 2012 19:35:56 GMT

I probably don't understand what you're asking for, but here's a graph that shows detail plus selectable weekly trend lines without using macros or set analysis. You can show the detail for all weeks, but a trend line for the first week, and a separate trend line for the third week. I've used gray for the section of the trend line representing the days used to calculate that trend. There may be better ways to make that information clear - it was just the first thing that occurred to me. This approach is using some data connected by if(), so it would be probably be unacceptably slow for large data sets.

Re: How do you toggle between expressions for sorting in a bar chart

quiquehm — Thu, 01 Mar 2012 22:40:46 GMT

Thank you John ! It really looks like the way I need it. Based on the selected values of a Field ( In your case TrendLine ) you show/hide the corresponding trend lines on the chart. The only problem is in my case I am not too sure how to configure the script, I believe it´s maybe much more simple : The data I have is as follows :

DataSet	Jan	Feb	March	Apr	May
FW07	20	15	20	10	50
FW08	20	15	25	15	55
FW09	15	15	20	12	33
FW10	15	15	20	12	33
FW11	20	15	25	15	55

So, in my case the Field where I would select the trend lines to be displayed is DataSet. I know how to convert above table to a tabular list ( using load crosstable ), so I would have 3 fields ( DataSet, Month, Sales ). For the sales amounts I would need to set Accumulation in the Expression settings ( to have a proper trend line, accumulating the values ). The DataSet just represents the data extraction from SAP ( one snapshot report per week for example..this is why these are called FW07, FW08 ....etc ( fiscal weeks ).

I hope it is better explained now . Any help of setting up the script/chart using this table ? Thanks a lot for your help

Enrique

Re: How do you toggle between expressions for sorting in a bar chart

johnw — Fri, 02 Mar 2012 23:06:42 GMT

Let's say I select FW07 and FW08 for that data. A simple line chart with a default trend line and full accumulation does this.

I assume that's not what you want, but what do you want it to look like instead?

Re: How do you toggle between expressions for sorting in a bar chart

quiquehm — Sat, 03 Mar 2012 00:01:52 GMT

Sorry for the misunderstanding.I used the words trend lines incorrectly.What I simply need are the 2 lines in the chart, one for FW07 and one for FW08...or for any other FW selected.How can i setup the script for that?thanks for the help

Re: How do you toggle between expressions for sorting in a bar chart

johnw — Sat, 03 Mar 2012 00:30:42 GMT

If I understood, then you set it up in the script as you've already said:

"( using load crosstable ), so I would have 3 fields ( DataSet, Month, Sales )"

The chart would have dimensions Month and DataSet, expression sum(Sales), and have full accumulation.

It may take a little effort to get your Month field working right. The attached example shows one way, but it makes some assumptions about how your data is really set up. Here's the script, and there might be a way to do it in one step, but doing a preceeding load with a crosstable has never worked for me for whatever reason.

SalesTemp:

CROSSTABLE (Month, Sales)

LOAD * INLINE [

DataSet 'Jan 2012' 'Feb 2012' 'Mar 2012' 'Apr 2012' 'May 2012'

FW07 20 15 20 10 50

FW08 20 15 25 15 55

FW09 15 15 20 12 33

FW10 15 15 20 12 33

FW11 20 15 25 15 55

] (delimiter is ' ')

;

Sales:

NOCONCATENATE

LOAD

DataSet

,date(date#(Month,'MMM YYYY'),'MMM YYYY') as Month

,Sales

RESIDENT SalesTemp

;

DROP TABLE SalesTemp

;

Re: How do you toggle between expressions for sorting in a bar chart

quiquehm — Tue, 06 Mar 2012 12:12:11 GMT

John, thanks a lot for your help. In reality it was not that complicated to setup, your script is crystal clear. The thing is the more functions I discover in Qlikview the more difficult way I try to do things...and it shouldn´t be like that. Anyway what I would like to do next is a deviation analysis between two datasets I select ( for example if I select FW10 and FW09 I would like to see the "delta" sales between these two datasets. I modified a bit your qvw file and setup a combo chart, to display bars for the monthly sales forecast ( no accumulation ) and a line for accumulated sales forecast. Then I would like to display the monthly difference between the bars ( deviation graph ).

One way I thought about doing it was using set analysis for the bars expression, for example like :

=sum( {<DataSet={FW10}>} Sales ) - sum( {<DataSet={FW09}>} Sales ).

The problem with this is that expression is static ( I mean if I change my DataSet selections to other two pairs of weeks like FW11 and FW10, nothing would change in the chart ). Do you know how I could make these set analysis "kind of dynamic" to use the selections I make in the DataSet field....like :

=sum( {<DataSet={Whatever field value1 I select in DataSet field}>} Sales ) - sum( {<DataSet={Whatever field value2 I select in DataSet field}>}Sales )

( being field value1 bigger than field value 2 of course ). Also if this is possible ....would it also be possible once you select a field value 1 ( for example FW10 ) that the expression used for field value 2 the value inmediately preceding field value 1 ( in this case FW09 ) ....or FW08 in case FW09 did not exist in the list ? could this behaviour be automated in the expression somehow ?

Again, thanks for your time and your great help in this forum

Best Regards

Enrique

P.D.:One little detail is an annoyance I find in 2 line charts when values are too close , obviously the numbers on data points overlapp ..making difficult reading...any solution to this ?

Re: How do you toggle between expressions for sorting in a bar chart

johnw — Tue, 06 Mar 2012 17:42:24 GMT

I don't know how to keep the numbers from overlapping. QlikView seems a bit lacking in that regard.

To compare two selected data sets:

sum({<DataSet={'$(=maxstring(DataSet))'}>} Sales) -
sum({<DataSet={'$(=minstring(DataSet))'}>} Sales)

To compare a data set to the previous data set, I'd probably create a data set sequence so as to not be messing around with strings. But here's a string approach:

sum({<DataSet={'$(=maxstring(DataSet))'}>} Sales) -
sum({<DataSet={'$(='FW'&num(right(maxstring(DataSet),2)-1,'00'))'}>} Sales)

See attached.

Re: How do you toggle between expressions for sorting in a bar chart

quiquehm — Fri, 09 Mar 2012 13:54:36 GMT

Thank you John, it´s great, it does exactly what I need. The only issue I found is when I remove one of the DataSets ( for example FW08 ) from the script and leave the others ( FW07, FW09, FW10 and FW11 ) ( it may happen that on FW08 there was not a DataSet available ) , obviously ( and looking at your string approach below ), when selecting FW09, it does not work ( because it is expecting to find FW08 to make the calculation ), so it just displays the values on FW09 and not the difference between FW09 and FW07 ( the inmediate preceding available DataSet ).

Not sure there is a workaround for that.

Thanks again for your expert help. I attached the qvw file where I removed FW08 on purpose.

sum({<DataSet={'$(=maxstring(DataSet))'}>} Sales) -
sum({<DataSet={'$(='FW'&num(right(maxstring(DataSet),2)-1,'00'))'}>} Sales)

Re: How do you toggle between expressions for sorting in a bar chart

johnw — Fri, 09 Mar 2012 15:51:06 GMT

The workaround is doing what I suggested, "create a data set sequence". The sequence number can be sequential, and thus not have the same problems that the text description has.

Re: How do you toggle between expressions for sorting in a bar chart

quiquehm — Mon, 12 Mar 2012 08:22:59 GMT

Thanks, I will try to figure out how to do the sequence. I understand basically I need to replace the sequence FW07, FW08, FW10,FW11,FW13.....etc by simple sequential numbers 1,2,3,4,5,6,...etc. I will see if I can create another field called DataSet_Seq that autoincrements by 1 everytime a new DataSet is available.

Thanks again for your time and all the tips and tricks.

Enrique

Re: How do you toggle between expressions for sorting in a bar chart

johnw — Mon, 12 Mar 2012 17:06:36 GMT

If figuring out a sequence number when adding each data set proves difficult, I think that you could do this after they've all been loaded. I'm not certain what sort order the fieldvalue() uses. My guess is load order or maybe alphabetical. That might or might not be right, so you might need to add some sorting.

DataSetSequences:
LOAD
recno() as DataSetSequence
,text(fieldvalue('DataSet',recno())) as DataSet
AUTOGENERATE fieldvaluecount('DataSet');