7 Replies Latest reply: Apr 21, 2011 4:37 AM by DavinRM21 RSS

    Getting buttons to alter formats and filter data selection

      Hi,

      Really new to qlikview so sorry if this is a basic question, but I was hoping to have a series of buttons which when clicked would change the caption colour and data selections used in charts.

      To give a bit more context I have some data which looks something like this:

       

      Contact_idProduct TypePurchases 2009Purchases 2010Type2009Type2010
      1Banana01Non purchaserNew purchaser
      1Apple21New purchaser2yrs consecutive
      1Strawberry40New purchaserLapsed Purchaser
      1All fruit62New purchaser2yrs consecutive


      And what I was hoping to be able to create was 4 buttons (labelled banana / apple / strawberry / all fruit) each of which would have a colour (i.e. yellow for banana).

      I would then need the colour of the chart to be the same as the colour of the button clicked upon, and to only show data relating to that field, so if the user clicks on the yellow banana button, they only see customer data for bananas and all chart headers appear yellow.

      Is this something that is possible within qlikview?

      Thanks!

      Davin :)

        • Getting buttons to alter formats and filter data selection
          Karl Pover

          Hello Davin,

          The field selection is easy since you can create an action in the button that once clicked selected values in a field. The action is call Select in Field in the group Selection.

          Changing the color is not as direct, but it's still possible. First, create a variable called vColor, and then, for example, in the banana button create an action called Set Variable in the External group and assign the vColor variable the value yellow(). Finally, in the caption tab of the chart properties window, click on the background color and enable the Calculated radio button. Put the expression =$(vColor) in the expression field and you should now have a caption color that changes when you click the banan button.

          Regards.

            • Getting buttons to alter formats and filter data selection
              John Witherspoon

              I wouldn't use buttons. This sounds like standard selection logic, so I would simply use a list box. Get your users used to the standard behavior of QlikView rather than launching right into making things behave differently than default. You'll make things much easier on yourself if you can later add a new product type by, well, doing nothing.

              For the chart header, I'd likely load in the colors as script.

              [Product Type Colors]:
              LOAD * INLINE [
              Product Type, R, G, B
              Banana, 255, 255, 200
              Apple, 200, 255, 200
              Strawberry, 255, 200, 200
              All fruit, 200, 200, 200
              ];

              And then just use the values from the script (when specified) as the caption background color:

              if(len(R),rgb(R,G,B))

              If you're fairly certain you won't be adding new product types, I might settle for skipping that part of the script and just using this instead:

              pick(match("Product Type",'Banana','Strawberry','Apple','All fruit'),rgb(255,255,200),rgb(255,200,200),rgb(200,255,200),lightgray())

              And if you really MUST have buttons, I'd set them up with actions to do the selection for you as Karl said. I think that's a waste, though, both of screen real-estate and of effort. The example includes one such button. I wouldn't use the variable approach for color because if you, say, hit clear, the caption would still be yellow because clear doesn't clear variables. I assume we want the color connected to the Product Type itself, and saying "change color when I press the button" was just shorthand for that.

              See attached.

                • Getting buttons to alter formats and filter data selection

                  Hi John,

                  Thanks for this, it looks like it would do the job fine, and I agree that for the extra effort of using buttons it's probably not worth it!

                  Could I ask for a little explanation of how the

                  if(len(R),rgb(R,G,B))

                  pick(match(...

                  work?

                  Thanks for your help :)

                    • Getting buttons to alter formats and filter data selection
                      John Witherspoon

                      Sure! Let's start with this one, which I've just realized has a minor bug.

                      if(len(R),rgb(R,G,B))

                      QlikView wants an expression that returns a color. There are a number of color functions, like rgb(), argb(), yellow() and so on. We're using rgb(), which requires three parameters between 0 and 255. Our inline table gives us the values we'd like to use for each of these parameters. Now, normally, with no product type selected, a reference to a field like R will return null, because there are multiple possible values. But if we select a product type, now only one value of R is possible, so it will now be a number. The number could be 0, so we can't just check for R. Instead, we want to check that R is non-null. For that, you might think we'd use "not isnull(R)", except that isnull() doesn't work properly in 64 bit, or at least didn't. I haven't checked version 10. So I've gotten in the habit of using len() as my null test. Len(null()) = 0. Len(anything else) > 0. So basically, if(len(R)...) is my shorthand way of saying "if a product type is selected".

                      The bug is that I should have just checked product type. If two product types have the same R value, and we select just those two product types, len(R) will be > 0, but we do NOT want to use the color expression. We could do this:

                      if(len("Product Type"),rgb(R,G,B))

                      But I think it would actually be better to do this:

                      if(len(R) and len(G) and len(B), rgb(R,G,B))

                      The idea here is that if you select two product types with the exact same color, we might as well display that color.

                      There's no "else" value for our if, so if we haven't selected a product type, it returns null(). In a color expression, null() means "use whatever color you would have used if this expression isn't here". So it defaults the caption background to the normal caption background color.

                      OK, now on to this expression:

                      pick(match("Product Type",'Banana','Strawberry','Apple','All fruit'),rgb(255,255,200),rgb(255,200,200),rgb(200,255,200),lightgray())

                      First, let's consider the match() function. It returns the position of the first match in the list. So in this case, it will return 1 if Product Type = 'Banana', 2 for Strawberry, etc. The pick() function, on the other hand, returns the value in the given position. So it will return rgb(255,255,200) for 1, rgb(255,200,200) for 2, and so on. Put them together, and it's a way of saying "if product type is banana, use color rgb(255,255,200), else if product type is 'Strawberry', use color rgb(255,200,200)" and so on. Now, we COULD have written that as a nested if, which would have been clearer:

                      if("Product Type"='Banana' ,rgb(255,255,200)
                      ,if("Product Type"='Strawberry',rgb(255,200,200)
                      ,if("Product Type"='Apple' ,rgb(200,255,200)
                      ,if("Product Type"='All fruit' ,lightgray()))))

                      But a pick(match()) is a commonly-used shorthand, and one I think people should get used to seeing regardless of how they personally choose to code it.

                  • Getting buttons to alter formats and filter data selection

                    Hi Karl,

                    Thanks for the advice - is the action feature something which is available in qlikview 8.5 (as this is the version I am working with) as I can't see it when clicking on the properties box of a button.

                    Would it be possible to include a default colour scheme so that when the data is cleared the colour of captions would go back to this colour (in this case the 'all fruit' colour would be the default if no other fruit type would be selected.

                    Thanks again,
                    Davin :)

                      • Getting buttons to alter formats and filter data selection
                        John Witherspoon

                         


                        DavinRM21 wrote:is the action feature something which is available in qlikview 8.5 (as this is the version I am working with) as I can't see it when clicking on the properties box of a button.


                        Actions were introduced in version 9 as I recall. For version 8.5, you'd have to code visual basic macros. They're pretty simple macros, but they come with some significant disadvantages. I think in 8.5 ANY macro still flushed all cached data. So your user might be going between charts, and everything's cached so it's fast, and then they hit a button, and poof, all the charts slow to a crawl until QlikView gets them cached again. Click another button, slow again. Now, I've used lots of macros, and continue to do so, and haven't found this to be a big problem in practice, but it's still at least a bit of a problem. So all the more reason to do this with a list box instead of buttons, I'd say.

                        I think the consensus advice about macros is that if you have any other practical way to solve the problem, do it some other way. A macro is a powerful tool that is sometimes necessary, but reserve it for when it's truly necessary. Think of it as a sledgehammer or a chain saw when you typically want a scalpel.