topic Top Nth value in text object in QlikView

Top Nth value in text object

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

We have a chart, where we have kept the dimension limit as 7.

We want to showcase the 1st to 7th value in three different text objects, from the chart.

I have used the FirstSortedValue() function, and it works fine for 1st to 4th.

For 5th, 6th and 7th value, it giving me 'Null' values in text object, as all of them have same metric value.

As per one of the post, I have tried to use 'DISTINCT' keyword, still values not matching with chart.

Could you please assist me on same.

Thanks & Regards,

Re: Top Nth value in text object

sunny_talwar — Wed, 04 May 2016 11:00:00 GMT

Can you share the expression you are using in the chart and in the text box object?

Re: Top Nth value in text object

florentina_doga — Wed, 04 May 2016 11:01:23 GMT

or aggr functiin. pls add am exemple.

Re: Top Nth value in text object

trdandamudi — Wed, 04 May 2016 11:01:25 GMT

Please share your current expression.

Re: Top Nth value in text object

Anonymous — Wed, 04 May 2016 11:04:59 GMT

in the chart -

count({<Dim2={'A'}>}Dim1) /

count(TOTAL {<Dim2={'A'}, Dim3=>}Dim1)

This is the because, the chart based on Dim3 and I want to have percentage w.r.t others.

In Text Object-

FirstSortedValue(DISTINCT Dim3,

aggr(count({<Dim2={'A'}>}Dim1) /

count(TOTAL {<Dim2={'A'}, Dim3=>}Dim1) ,Dim3),5)

I have tried this, sorry, as i have not written the aggregated dimension name

Re: Top Nth value in text object

sunny_talwar — Wed, 04 May 2016 11:07:15 GMT

Can you try this:

FirstSortedValue({<Dim2={'A'}>} DISTINCT Dim3, Aggr(Count({<Dim2={'A'}>}Dim1)/Count(TOTAL {<Dim2={'A'}, Dim3=>}Dim1), AggrgatingDimensionNameHere) ,5)

Re: Top Nth value in text object

Anonymous — Wed, 04 May 2016 11:12:51 GMT

I tried that, but no luck...

I tried to get the rank in chart, and the rank is 4-7 for 4th to 7th value.

When we consider Dimension limit, by default, 7 values coming.

Need inputs for 5th, 6th and 7th.

Re: Top Nth value in text object

Kushal_Chawda — Wed, 04 May 2016 11:14:50 GMT

try this

or define your actual Dimension in Aggr fucntion

FirstSortedValue(DISTINCT Dim3,

aggr(count({<Dim2={'A'}>}Dim1) /

count(TOTAL {<Dim2={'A'}, Dim3=>}Dim1),Dim3) ,5)

Re: Top Nth value in text object

Kushal_Chawda — Wed, 04 May 2016 11:17:39 GMT

Please provide some of the sample data with expected output so that it will be easy to provide the solution

Re: Top Nth value in text object

sunny_talwar — Wed, 04 May 2016 11:24:20 GMT

Did you replace this -> AggrgatingDimensionNameHere with the chart dimension/s?

Re: Top Nth value in text object

Anonymous — Wed, 04 May 2016 11:29:20 GMT

yes

Re: Top Nth value in text object

sunny_talwar — Wed, 04 May 2016 11:32:21 GMT

Would you be able to provide a sample?

Preparing examples for Upload - Reduction and Data Scrambling

Re: Top Nth value in text object

Anonymous — Wed, 04 May 2016 11:42:44 GMT

Nope I tried to do same, but, in that case, I will need to change the expression values as well

Re: Top Nth value in text object

sunny_talwar — Wed, 04 May 2016 11:54:41 GMT

You mean when you try to create a sample using the method mentioned in the link you will have to change the expression? I don't think so

Re: Top Nth value in text object

Anonymous — Wed, 04 May 2016 12:02:44 GMT

I think we cant do that,

Re: FirstSortedValue on Calculated Position

Re: Top Nth value in text object

sunny_talwar — Wed, 04 May 2016 12:04:21 GMT

Unless we see what you are seeing right now, it would be very difficult to comment. But if you think your scenario matches with that guys scenario, then it might be right. Again, very difficult to say without seeing it.

Re: Top Nth value in text object

sunny_talwar — Wed, 04 May 2016 12:05:45 GMT

Would you be able to provide 10-15 lines of raw dummy data and your expected output from the data that you provide?

Re: Top Nth value in text object

Anonymous — Wed, 04 May 2016 13:20:44 GMT

Thanks to all, for your reply.

finally I found a solution using below link:

Displaying the Top Salesperson in a Textbox

Just a change in the expression of Rank, we will have to add the Offset value as 4, to get the value in text object.