topic Re: Ignoring Selections - Median in QlikView

Ignoring Selections - Median

Tue, 07 Jul 2015 23:08:40 GMT

Hi everyone,

I have a very complicated expression that I am using to generate a median line on my chart. I have a requirement to make sure the median does not change when values in Market dimension are selected. How/where do I specify in the expression to ignore selections in that dimension?

=Median( TOTAL

aggr(

(3-(3*((aggr(rank([Est Market Size],2),MarketID))/Count(TOTAL(MarketID)))))*avg({<Factor={'Market Size'}>}Weightings)

(3-(3*((aggr(rank([Est Market Growth],2),MarketID))/Count(TOTAL(MarketID)))))*avg({<Factor={'Market Growth'}>}Weightings)

([OfferingMarket Fit]*avg({<Factor={'Market Fit'}>}Weightings))

([Type of Market]*avg({<Factor={'Type of Market'}>}Weightings))

([Customer Requirements]*avg({<Factor={'Customer Requirements'}>}Weightings))

([Competitive Dynamics]*avg({<Factor={'Competitive Dynamics'}>}Weightings))

,[Tier 4 - Market Segment]))

It is not an option to pre-calculate the median and write it as a value.

Thanks!

Re: Ignoring Selections - Median

sunny_talwar — Wed, 08 Jul 2015 01:24:47 GMT

Try this may be:

=Median({<Market = >} TOTAL

aggr(

(3-(3*((aggr(rank(Only({<Market = >}[Est Market Size]),2),MarketID))/Count({<Market = >}TOTAL(MarketID)))))*avg({<Factor={'Market Size'}, Market = >}Weightings)

(3-(3*((aggr(rank(Only({<Market = >}[Est Market Growth]),2),MarketID))/Count({<Market = >}TOTAL(MarketID)))))*avg({<Factor={'Market Growth'}, Market = >}Weightings)

(Only({<Market = >}[OfferingMarket Fit])*avg({<Factor={'Market Fit'}, Market = >}Weightings))

(Only({<Market = >}[Type of Market])*avg({<Factor={'Type of Market'}, Market = >}Weightings))

(Only({<Market = >}[Customer Requirements])*avg({<Factor={'Customer Requirements'}, Market = >}Weightings))

(Only({<Market = >}[Competitive Dynamics])*avg({<Factor={'Competitive Dynamics'}, Market = >}Weightings))

,[Tier 4 - Market Segment]))

Everything in red is the new stuff. I hope nothing is broken and hopefully it will work.

Best,

Sunny

Re: Ignoring Selections - Median

Wed, 08 Jul 2015 13:24:14 GMT

Thank you for your reply Sunindia. Unfortunately, your solution didn't work when I made a selection, although the number seemed to move less.

Your solution seemed to cover everything, I don't understand why it doesn't work!

Re: Ignoring Selections - Median

sunny_talwar — Wed, 08 Jul 2015 13:26:52 GMT

I might have missed something. I can look again right now. Let me see and get back to you.

Best,

Sunny

Re: Ignoring Selections - Median

sunny_talwar — Wed, 08 Jul 2015 13:31:39 GMT

Check this one out:

=Median({<Market = >} TOTAL Aggr(

(3-(3*((

Only({<Market = >} Aggr(Rank(Only({<Market = >}[Est Market Size]), 2), MarketID)))/

Count({<Market = >}TOTAL(MarketID))))) * Avg({<Factor={'Market Size'}, Market = >}Weightings)

(3-(3*((

Only({<Market = >} Aggr(rank(Only({<Market = >}[Est Market Growth]),2),MarketID)))/

Count({<Market = >}TOTAL(MarketID)))))*avg({<Factor={'Market Growth'}, Market = >}Weightings)

(Only({<Market = >}[OfferingMarket Fit]) * Avg({<Factor={'Market Fit'}, Market = >}Weightings))

(Only({<Market = >}[Type of Market]) * Avg({<Factor={'Type of Market'}, Market = >}Weightings))

(Only({<Market = >}[Customer Requirements]) * Avg({<Factor={'Customer Requirements'}, Market =>}Weightings))

(Only({<Market = >}[Competitive Dynamics]) * Avg({<Factor={'Competitive Dynamics'}, Market =>}Weightings))

,[Tier 4 - Market Segment]))

Best,

Sunny

Re: Ignoring Selections - Median

Wed, 08 Jul 2015 14:07:35 GMT

Hi Sun,

That didn't work either. I think that this is because I am fundamentally not aggregating correctly.

In the original formula, I am calculating 6 different scores for each market and adding them together. That formula only works when I only have one Market selected.

The real question is, how do I write a formula to calculate the scores for each Market, even with no selections?

Anyway, I will have to return to this later step-by-step. Thank you for your time!

Re: Ignoring Selections - Median

sunny_talwar — Wed, 08 Jul 2015 14:13:32 GMT

I may be help if you can share a sample with your expected output.

Best,

Sunny

Re: Ignoring Selections - Median

Wed, 08 Jul 2015 14:17:22 GMT

I completely agree! However, I am facing some deadlines right now - when I come back to this, I will post some sample data in an app for you. Thanks!

Re: Ignoring Selections - Median

sunny_talwar — Wed, 08 Jul 2015 14:18:56 GMT

Works for me

Best,

Sunny