how to create the top scorer in a text box !!

[fashid]

Hi,

I have created a scorecard which has 6 different pivot charts overlapped on one another representing parameters.

The last column is MTD score which changes on branch selection.

I need to get the top scoring branch in a text box to be displayed each month.

The top score should be based on the average score of all the parameters.

eg if branch del has average mtd score of all the parameters as 10

and branch  mum has average mtd score of all the parameters as 6

then the top branch would be delhi.

How could i go about doing this.

I have attached  the sample qvw which i am working on

Regards,

Nadeem

Message was edited by: nadeem shaikh

[Gysbert_Wassenaar]

Please read these two documents:

• QlikCommunity Tip: How to get answers to your post?
• Preparing examples for Upload - Reduction and Data Scrambling

[fashid]

Hi Gysbert,

Sorry, My Bad I went through the links provided by you and have uploaded a sample document that i am working on with reduced data.

Regards,

Nadeem

[Gysbert_Wassenaar]

Can you explain in more detail what you want to calculate? Perhaps with an example result table?

[fashid]

Hi Gysbert,

Hi Gysbert, If you see the last column, the last column has formula for the MTD score .

The branch list box allows us to see the MTD score for each branch.

Now i want a text box which can tell us which branch has the highest score .

I know it is possible in a pivot chart but is it possible to achieve the result in a text box

I am attaching a sample view oh how it is required in the sheet.

Regards,

Nadeem

[Gysbert_Wassenaar · Accepted solution]

Ok, I understand what you're trying to do. It's going to get complicated. Your MTD score expression uses Column(3) to reference another expression. That won't work for the expression you need. So you'll have to create MTD score expressions that only reference fields (the text box won't have columns). You want the average of the 6 (?) MTD scores, right? But for some branches there will be MTD scores missing. You'll have to consider how to deal with those. You could calculate the average in several ways:

rangeavg( MTDScore1 , MTDScore2 , MTDScore3 , MTDScore4 , MTDScore5 , MTDScore6 )

or

rangesum( MTDScore1 , MTDScore2 , MTDScore3 , MTDScore4 , MTDScore5 , MTDScore6 ) / 6

Replace MTDScore1,2 etc with the expressions from the pivot tables that calculate the MTD.

Once you have that rangeXXX( ....) expression you can calculate the best scoring branch with

=max(aggr( rangexxx( ... ) , branch))

To keep the expression readable consider putting (sub)expressions in variables and use the variable in the expression (and in the pivot tables)

Simple example:

variable vSumSales: sum(Sales)

expression using the variable: =max(aggr( $(vSumSales), branch))

[fashid]

Hi ,

I Salute you !!!

Thanks a lot i got it going and created the formula the way you told.

The formula that i have created is just for the last two parameters claims and TAT 5 for testing purposes

The top score that i have got is for the BLR branch.

Now it displays the top score but how do i get the branch name.

I have attached the updated qvw which has the formula for aggr score.

I am almost there all thanks to you

Regards,

Nadeem

[Gysbert_Wassenaar]

If you want the branch name you use the FirstSortedValue function instead of max. You can find an explanation here: FirstSortedValue function in QlikView – Part I (at QlikShare)‌. There's also a Part II video that you probably need to see too.

[fashid]

Hi,

I tried the first sorted value but unfortunately i was unable to make it work.

Hope u can have a look at my formula.

Regards,

Nadeem

[Gysbert_Wassenaar]

The firstsortedvalue by default sorts in ascending order. You need to a minus sign, a - , before the aggr(). If that doesn't help try creating a straight table with branch as dimension and the rangesum(....) expression as chart expression and see if that returns results.