Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Ignoring Selections - Median

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!

8 Replies
sunny_talwar

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

Not applicable
Author

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!

sunny_talwar

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

Best,

Sunny

sunny_talwar

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

Not applicable
Author

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!

sunny_talwar

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

Best,

Sunny

Not applicable
Author

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!

sunny_talwar

Works for me

Best,

Sunny