topic Re: Claculating median for background color expression in QlikView

Claculating median for background color expression

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

Hi Folks,

Sunny Talwar

Gysbert Wassenaar

Marcus Sommer

jagan mohan rao appala

Henric Cronström

We need to calculate median for a column to represent the values with Background color of cells in straight table.

As we know that we are having Median function and those calculations are possible in Excel.

I want to know that the same activity is it possible in Qlikview too?

I am explaining the sample data here.

I am having two columns 'Team' and 'Time', I need to calculate median of 'Time' first and need to allocate a color to it.

A few formulas have been implemented in Excel , the same I need to show in QV for team wise (Low, Medium, High).

PFA sample data and screenshots for the same and advise.

Thanks

Krishna

Re: Claculating median for background color expression

vinieme12 — Tue, 23 Jan 2018 09:34:08 GMT

You can directly use MEDIAN(TIME) as expression

Re: Claculating median for background color expression

vinieme12 — Tue, 23 Jan 2018 09:35:35 GMT

Refer help section

Median - chart function ‒ Qlik Sense

Stdev - chart function ‒ Qlik Sense

and always search the community for queries first, it will save you time as most of the queries are already answered

Re: Claculating median for background color expression

sunny_talwar — Tue, 23 Jan 2018 09:36:31 GMT

Do you only have Team as your dimension or do you have more dimensions?

Re: Claculating median for background color expression

sunny_talwar — Tue, 23 Jan 2018 09:37:11 GMT

May be you need something like this in case you have more than one dimension in your chart

Median(TOTAL <Team> TIME)

Re: Claculating median for background color expression

krishna20 — Tue, 23 Jan 2018 09:42:59 GMT

Hi Vinneth,

Thanks for immediate response . I think you have missed something in my sample data.

I need to represent the color for team wise in background color expression.

And as well standard deviation function has been used to represent the colors.

Please suggest accordingly.

Re: Claculating median for background color expression

krishna20 — Tue, 23 Jan 2018 10:00:20 GMT

Thanks Sunny for replying. How can I assign the colors for those values. Can you please help me with a sample data or app.

Re: Claculating median for background color expression

krishna20 — Tue, 23 Jan 2018 10:40:10 GMT

Hi ,

I need to represent the colors based on the formula as I attached the screenshots.

Re: Claculating median for background color expression

sunny_talwar — Tue, 23 Jan 2018 12:07:18 GMT

I mean not entirely sure what you have... but it would be some kind of if condition

If(Expression > Median, Color1, Color2)

Something along those lines, but I don't have a lot of information from you to give you an expression you would need for your case. Do you want to share a sample?

Re: Claculating median for background color expression

krishna20 — Tue, 23 Jan 2018 12:51:45 GMT

Hi Sunny,

I am attaching here the sample data and I need to implement the same in QV.

Please let me know if you need any further details.

Re: Claculating median for background color expression

sunny_talwar — Tue, 23 Jan 2018 12:55:24 GMT

Implement what? I don't see any colors here? are you talking about this?

Re: Claculating median for background color expression

krishna20 — Tue, 23 Jan 2018 13:20:25 GMT

Hi Sunny,

Sorry, in previous file formula was wrong for Low, Medium, High.

Now I have corrected. I am attaching the file again.

What I exactly want like below.

eg for Medium:

Team_1 --> Medium (formula)-->ROUND(MEDIAN(C$2:C$13),2).

Here C is Time.

We need to represent the bgcolor for the resultant value of median in QV.

Like this for Team_2 and Team_3 need to assign color dynamically for the values of Dimension Team.

Re: Claculating median for background color expression

sunny_talwar — Tue, 23 Jan 2018 13:41:20 GMT

But where do you have color my friend? I think I am unable to get my point across, but where are you adding the colors?

Re: Claculating median for background color expression

krishna20 — Wed, 24 Jan 2018 06:58:03 GMT

Hi Sunny,

If you see in the attached excel there is three different Icons that are changing based on values. PFA for reference.

We need to implement those in QV .

We are not taking those images in QV, just we need to represent the cell values in colors like blue, green and yellow.

Re: Claculating median for background color expression

krishna20 — Wed, 24 Jan 2018 13:09:53 GMT

Hi,

Can anyone show light on this issue?

Re: Claculating median for background color expression

sunny_talwar — Thu, 25 Jan 2018 13:28:00 GMT

This?

Re: Claculating median for background color expression

krishna20 — Mon, 29 Jan 2018 05:17:56 GMT

Thanks a lot sunny