topic How to add function in the set experssion in QlikView

How to add function in the set experssion

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

Could you please help me out in this scenario!!

variables:

Top=2

Bottom=12

Incident _Id is filed name(Contains unique values)

I have a set expression like this " =ONLY({$< Incident _Id={'<$(=$(=Top))>$(=$(=Bottom))'} >} Incident _Id)" in straight table expression.

Incident _Id should be replaced with " Aggr(RowNo(),Incident _Id) "

I tried from my side but I am not getting the result.

Insight: The range should be reflected in the straight table

Regards,

Sanal

Re: How to add function in the set experssion

sushil353 — Thu, 17 Nov 2016 08:52:53 GMT

Try this:

Max({$< Incident _Id={"<$(Top) >$(Bottom)"} >} Aggr(RowNo(),Incident _Id))

Re: How to add function in the set experssion

kaushiknsolanki — Thu, 17 Nov 2016 08:53:41 GMT

Hi,

can you explain the reason or the logic behind using the aggregation function with example.

Regards,

Kaushik Solanki

Re: How to add function in the set experssion

Thu, 17 Nov 2016 09:48:03 GMT

Only the incident id from 2nd row to 12 row should be visible in the straight table by using this expression.

Top and Bottom value dynamically will change according to the selection in the filter.

Re: How to add function in the set experssion

kaushiknsolanki — Thu, 17 Nov 2016 09:53:51 GMT

Hi,

Are you saying that the order in which it has been loaded, from there it should pick up the 2 to 12th record.

Or is there any sorting criteria like top 20 by sales, or by date or any other sort criteria.

Regards,

Kaushik Solanki

Re: How to add function in the set experssion

Thu, 17 Nov 2016 10:03:39 GMT

Are you saying that the order in which it has been loaded, from there it should pick up the 2 to 12th record-Yes

I need the result that should exclude top 1% and bottom 1% from the straight table.

In the above example if it is 100 count(count of Incident_Id), then 1 from top and 1 from bottom should be excluded.

Re: How to add function in the set experssion

kaushiknsolanki — Thu, 17 Nov 2016 10:08:31 GMT

Hi,

Can you post some sample data along with the expected output from that data.

Regards,

Kaushik Solanki

Re: How to add function in the set experssion

tresB — Thu, 17 Nov 2016 10:15:15 GMT

You would probably need to use rank() in the set analysis. RowNo() would not work here because the calculation for this function works once the the chart is ready, i.e. - before you get the chart there is nothing to rowno(), hence you can't restrict based on that. Try to share a sample qvw and explain your expected output against that, That would help us help you.