topic How-to-ignore-selection-when-using-Rank-and-Aggr in QlikView

How-to-ignore-selection-when-using-Rank-and-Aggr

sbkumar — Wed, 10 Jul 2019 05:46:28 GMT

hi friends

For an application, I need to ignore a certain search filter using Set Analysis into an aggregate function.

The AGGR function is:

Aggr(
if(rank(sum({<$(vTime_Frame)={1}, [Therapeutic Category] = {$(vMarketValue)},Territory=>}TRX)/sum({<$(vTime_Frame)={1},[Therapeutic Category] = {$(vMarketValue)},Territory=>} Total <IMS_ID_Searchable> TRX))<=5,
(sum({<$(vTime_Frame)={1}, [Therapeutic Category] = {$(vMarketValue)},Territory=>}TRX)/sum({<$(vTime_Frame)={1}, [Therapeutic Category] = {$(vMarketValue)},Territory=>}Total<IMS_ID_Searchable>TRX))),IMS_ID_Searchable,[Payer Channel])

if i remove Territory= data is coming correctly,if i include Territory= ,it is showing as NO Data to display.

Finally I need to Include Territory in the expression.

Re: How-to-ignore-selection-when-using-Rank-and-Aggr

saar_lorena — Wed, 10 Jul 2019 12:14:43 GMT

What if you include an Only() ?

Something like:

Aggr(Only(
if(rank(
sum({<$(vTime_Frame)={1}, [Therapeutic Category] = {$(vMarketValue)},Territory=>}TRX)
/
sum({<$(vTime_Frame)={1},[Therapeutic Category] = {$(vMarketValue)},Territory=>} Total <IMS_ID_Searchable> TRX))<=5,

(sum({<$(vTime_Frame)={1}, [Therapeutic Category] = {$(vMarketValue)},Territory=>}TRX)/sum({<$(vTime_Frame)={1}, [Therapeutic Category] = {$(vMarketValue)},Territory=>}Total<IMS_ID_Searchable>TRX))),IMS_ID_Searchable,[Payer Channel]))

If it doesn't work, can you upload a qv example here? Thanks 😃

Re: How-to-ignore-selection-when-using-Rank-and-Aggr

sbkumar — Wed, 10 Jul 2019 12:29:19 GMT

Thanks Saar_lorena but not its not working ...

Re: How-to-ignore-selection-when-using-Rank-and-Aggr

saar_lorena — Thu, 11 Jul 2019 07:25:01 GMT

Can you upload an example of your file here, please?