Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sunil2288
Creator III
Creator III

How to get RangeAvg with below() function in a Text object

Hello Everyone,
I have a real time problem. I have 3 different lines in a combo chart which has a weeknum as the dimension. The 1st and 3rd line are Thresholds and the 2nd line is a RangeAvg for last nth week. Here n is a variable. So if the user change the n-value the 2nd line also will change.
So when this 2nd line either cross 1st line the (upper threshold) or 3rd Line( the lower threshold) I need to take account (basically counting the points - outliers) of those points. So in the line chart I am able to get that information as I can apply RangeAvg along with below () function. But how can I get that count in a Text object? In a text object I can't use below() function. Is there any other way?

Comments much Appreciated.

Best Regards
Sunil
4 Replies
dplr-rn
Partner - Master III
Partner - Master III

You cannot use below function.
You will need to use set analysis which will use your n
sunil2288
Creator III
Creator III
Author

HI Dilip,

Thanks for your reply.
I have the expression like this now in the Line chart:-

if(Sum([Week's KPI Value])>=
  (RangeAvg(below(sum([Week's KPI Value]),-1,$(vnValue)))+
      ($(vyValue)*RangeStdev(below(sum([Week's KPI Value]),-1,$(vnValue))))),
    Sum([Week's KPI Value]),NULL())

You can see in this expression "vnValue" is the variable which has been taken inside a below() function as a parameter. BUt this below() function doesn't work in Textobject. Can you please share the Set Analysis expression for this above expression which I can put in the Text object ?

Best Regards
Sunil

jonathandienst
Partner - Champion III
Partner - Champion III

This is difficult to answer without a lot more context. For best results, i suggest that you upload a small .qvf file containing some representative sample data and the chart from which you have taken the expression. It need not be real data, but it must be in the same data model structure.

In the chart, the expression is evaluated for each dimension value. For which dimension value do you want to evaluate the text box? Above/below depend on the sort conditions - how is the chart sorted? etc etc

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
sunil2288
Creator III
Creator III
Author

Hi Jonathan,

Hope you are doing good. Thanks for your reply. Here is the detailed explanation of my problem statement.
The below line chart has 4 different measures, 1. Weekly KPI Value, 2. Upper Control, 3. Lower Control, 4. Outliers.

And all the expression are given in the Master items of the attached .qvf file. Please see the attachments.

So, I need to take the count of these red color dots in a Text object and show that as the Total No of Outliers. Please refer the attached application, you will be able to understand fully. Please select one Category, one Client and One KPI Name from the filter pane in the attached application.

OutliersinChart.JPG

 How can I attach the application in .qvf file?


Please let me know if you have any queries.

Best Regards

Sunil