4 Replies Latest reply: Feb 16, 2011 9:00 PM by John Witherspoon RSS

    A couple of questions about setting up reports



      Hi I am new to designing reports with Qlik and I had some questions, that I have not been able to find answers to. Any help will be greatly rewarded with my appreciation.



      I made a simple bar chart. It's the number of sales per product.


      When I select one of the products on the bar chart and then hover my mouse over it, a yellow box pops up that says


      "ProductDescription = X

      Count(Distinct Email) = Y"


      Where X is the product name, and Y is the resulting count. I do not want end users to see column names or sql code at all. Can I get rid of this box, or control what is displayed in it?



      The green update indicator circle that is supposed to appear next to a sheet's name doesn't seem to be working for me.


      I have Sheet1 and Sheet2


      If I change something on Sheet1 I see no green dot.


      If I change something on Sheet1 and click over to Sheet2, now a green dot appears on Sheet1.


      If I change something on Sheet2 I see no green dot.


      If I change something on Sheet2 and click over to Sheet1 I see no green dot.


      In all the cases I am dealing with the same two sheets and a selection on one sheet definitely changes data on the other. How exactly is that green indicator supposed to work?



      Re: Picking a log scale for Y-axis.

      It appears with certain selections the axis scale will change to be logarithmic. I have a stacked bar chart with purchase types. When I drill down into some purchase types a log scale is present, but sometimes it is not. Is there a way to force a log scale no matter what the selection?


      Is there a way to change the log base for the scale from 10 to say 2?



      When adding a trendline to a graph, you are forced to show the equation in order to show the R Squared value. Why? Is there a way to just show R Squared and not the equation itself?


      Is there a way to control the color of the trendline?

        • A couple of questions about setting up reports
          Vlad Gutkovsky
          1. Under Presentation, disable PopUp Labels.
          2. That's probably a bug. Make sure you're on the latest SR.
          3. Axis --> Log Scale. Also, lookup functions log() and log10() in the Help file.
          4. I don't think that's possible, no.


          • A couple of questions about setting up reports
            John Witherspoon

            1) Yes, you can control what displays in the pop up box. First, name your columns such that they make sense to users. If "ProductDescription" is meaningless to them, name the field "Product Name" or something. You CAN override this by giving the column a label in the chart itself, but I would avoid that, as you generally don't want multiple names for the same column. Pick a good name, put it in your script, and stick with it. As for count(distinct Email), add a label to your expression, like "Number of Email Addresses". When you give an expression a label, QlikView will show the label instead of the expression in the pop ups.

            2) It is not an update indicator. It is a selection indicator. And it doesn't show you a green dot on the CURRENT sheet. You're on the sheet, so you should be able to see that you've made selections on that sheet. The green dot only appears on OTHER sheets, the ones that aren't currently active, if there are fields selected on the other sheets. It doesn't matter if your selection changes data on the other sheets. It only matters if those sheets show that selection in a list box or multibox. I'm not sure that's how I'd have designed it, but I believe that's how it's working.

            3) You cannot force a log scale if any of the data points are zero. A zero data point on a logarithmic scale would be infinitely far below your monitor. It cannot be displayed. Rather than fail to display your data, it switches the scale to allow the data to be displayed. I suppose one way to force a logarithmic scale would be to change the expression to convert zero to an arbitrarily small value or maybe null.

            4) Yes, you can display just the R-squared value, but not with the checkbox unfortunately, so it gets a little complicated. Go to the Presentation tab and click on the Add button next to "Text in Chart". It will pop up a little box to enter the text you want to display. This allows you to enter a formula to compute the R-squared value. Let's say that our chart is showing sum(Sales) with a dimension of Month. The R-squared value is this:


            But you'll probably want text with it, and you'll probably want to format it. So enter something like this in the text box:

            ='R-squared = ' & num(pow(correl(aggr(sum(Sales),Month),Month),2),'#,##0.00000')

            Now, it looks like it wants to put the text in the upper left of the chart. To change that, select the chart and hold down the control and shift keys. Red boxes should appear around some of the chart elements, including your new R-squared text. You can drag and drop and resize the boxes.

            I don't think you can directly control the color of the trend line. QlikView really wants it to match your expression. But you can create your OWN trend line as an expression, and then you can color it any way you want just like any other expression. Another advantage is that it now lets you get the pop up information for the trend line itself, not just for your data. For my example, the trend line expression would be this:

            linest_m(total aggr(sum(Sales),Month),Month)*Month
            +linest_b(total aggr(sum(Sales),Month),Month)

            The attached example demonstrates both of these things.