topic Re: Color Mix Not updating background colors in QlikView

Color Mix Not updating background colors

Tue, 11 Nov 2014 23:54:05 GMT

Hello,

Can someone tell me what is wrong with this code? I'm trying to create background colors (mimic Excel) over a range of values (Ratio). I would like to set it up so

Upper is Max(Ratio)

Lower is Min(Ratio)

Intermediate is in between Upper and Lower

My code isn't working and I don't understand why.

I have attached an example.

Appreciate some help, please!

Re: Color Mix Not updating background colors

MK_QSL — Wed, 12 Nov 2014 00:33:06 GMT

Instead of Ratio, use SUM(Ratio) as Expression

Now Upper Limit and Lower Limit use as below

Max(Total Ratio)

Min(Total Ratio)

Re: Color Mix Not updating background colors

Wed, 12 Nov 2014 01:00:23 GMT

Thanks for the quick response! In my real application the Ratio is a variable. When I duplicate this in the app using the variable it does not place any background color on the table.

Sum($(vDocRatio))

Max(TOTAL $(vDocRatio))

Min(TOTAL $(vDocRatio))

Re: Color Mix Not updating background colors

Anonymous — Wed, 12 Nov 2014 01:04:12 GMT

Hey LR,

Please find attached qvw.

It is working infact. only issue is in your data you are having 0 values for Dimension value 'D' which is being supressed with default setting(suppress zero values). that's the reason you are not able to find the color setting for minimum value.

I have unchecked that check box.

Regards,

Chinna

Re: Color Mix Not updating background colors

Wed, 12 Nov 2014 01:25:00 GMT

I can successfully make it work in my sample but when I apply to my other application, it's not working.

I have a variable (vDocRatio) set up that calculates the ratio. It's defined

Avg({<DateComp={'Post'},[Issue in Exchg]={'N'}>}[Ttl Doc Amt USD]) / Avg({<DateComp={'Prior'},[Issue in Exchg]={'N'}>}[Ttl Doc Amt USD])

In color mix wizard, when I try to Sum($(vDocRatio)) I get no background color.

ColorMix1 ((1+Sign(2*(Sum($(vDocRatio))-RangeMin (top(total Sum($(vDocRatio)),1,NoOfRows(total))))/(RangeMax (top(total Sum($(vDocRatio)),1,NoOfRows(total)))-RangeMin (top(total Sum($(vDocRatio)),1,NoOfRows(total))))-1)*Sqrt(Fabs((2*(Sum($(vDocRatio))-RangeMin (top(total Sum($(vDocRatio)),1,NoOfRows(total))))/(RangeMax (top(total Sum($(vDocRatio)),1,NoOfRows(total)))-RangeMin (top(total Sum($(vDocRatio)),1,NoOfRows(total))))-1))))/2, ARGB(255, 255, 0, 0), ARGB(255, 0, 255, 0))

Re: Color Mix Not updating background colors

Wed, 12 Nov 2014 01:31:38 GMT

I can successfully make it work in my sample but when I apply to my other application, it's not working.

I have a variable (vDocRatio) set up that calculates the ratio. It's defined

Avg({<DateComp={'Post'},[Issue in Exchg]={'N'}>}[Ttl Doc Amt USD]) / Avg({<DateComp={'Prior'},[Issue in Exchg]={'N'}>}[Ttl Doc Amt USD])

In color mix wizard, when I try to Sum($(vDocRatio)) I get no background color.

Re: Color Mix Not updating background colors

Anonymous — Wed, 12 Nov 2014 01:55:08 GMT

Dear L R,

Please share your app, if possible.

Try to do following.

Try by removing sum in color mix wizard.

try the variable calculation directly in color mix wizard

Regards,

Chinna

Re: Color Mix Not updating background colors

Wed, 12 Nov 2014 02:48:45 GMT

Hello Chinna,

Attached is sample of what I'm referring to with the variables not working when attempting to set the background color. I've also tried using the expression contained in the variables.

Re: Color Mix Not updating background colors

Anonymous — Wed, 12 Nov 2014 11:18:32 GMT

Dear LR,

PFA. it is working now.

Re: Color Mix Not updating background colors

Thu, 13 Nov 2014 03:42:09 GMT

Hello Chinna

Almost there. I need to be able to set Upper, Lower and intermediate expressions for the background color. I tried using Max and Min like suggested above but I get an error in the expression. Suggestions?

Max(Total Avg({<DateComp={'Post'},[Exchanged]={'N'}>}[Amount]) / Avg({<DateComp={'Prior'},[Exchanged]={'N'}>}[Amount]))

Min(Total Avg({<DateComp={'Post'},[Exchanged]={'N'}>}[Amount]) / Avg({<DateComp={'Prior'},[Exchanged]={'N'}>}[Amount]))

Then for intermediate I need to max + min / 2

Thanks for your help!

Re: Color Mix Not updating background colors

jonathandienst — Thu, 13 Nov 2014 05:46:34 GMT

You need to use Aggr() to nest the Avg inside a Min or Max:

=Max(Aggr(Avg({<DateComp={'Post'},[Exchanged]={'N'}>}[Amount]) / Avg({<DateComp={'Prior'},[Exchanged]={'N'}>}[Amount]), Other))

=Min(Aggr(Avg({<DateComp={'Post'},[Exchanged]={'N'}>}[Amount]) / Avg({<DateComp={'Prior'},[Exchanged]={'N'}>}[Amount]), Other))

HTH

Jonathan

Re: Color Mix Not updating background colors

Thu, 13 Nov 2014 06:17:15 GMT

Hello

Thanks for the help. I was able to get it working by using

RangeMin (top(total Avg({<DateComp={'Post'},[Exchanged]={'N'}>}[Amount]) / Avg({<DateComp={'Prior'},[Exchanged]={'N'}>}[Amount]) ,1,NoOfRows(total)))

RangeMax (top(total Avg({<DateComp={'Post'},[Exchanged]={'N'}>}[Amount]) / Avg({<DateComp={'Prior'},[Exchanged]={'N'}>}[Amount]) ,1,NoOfRows(total)))

RangeAvg (top(total Avg({<DateComp={'Post'},[Exchanged]={'N'}>}[Amount]) / Avg({<DateComp={'Prior'},[Exchanged]={'N'}>}[Amount]) ,1,NoOfRows(total)))

I am going to try your suggestion tomorrow!

Re: Color Mix Not updating background colors

deepakqlikview_123 — Fri, 05 Jun 2015 07:42:12 GMT

Thanks Manish