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

[#1]

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. 

[quiquehm]

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 ?

[johnw]

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.

[quiquehm]

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)

[johnw]

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.

[quiquehm]

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

[johnw]

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');