topic Number formating with Variable Not working in QlikView

Number formating with Variable Not working

Fri, 11 Mar 2016 16:42:48 GMT

I created a variable to assign formats to each value based on the value ID. It works if I only select ONE component ID but when I have a group of ID's it fails and I will always have a group. I added the aggr function before and after min function and still does not work unless I select only one. What am I missing?

vComponentFormat is defined as follows

=if(aggr(min(ComponentFormat),COMPONENT_NAME_RELATED_INFO) = 1,'#,##0.;(#,##0.)',
if(aggr(min(ComponentFormat),COMPONENT_NAME_RELATED_INFO) = 2, '#,##0%',
if(aggr(min(ComponentFormat),COMPONENT_NAME_RELATED_INFO) = 3, '#,##0.0%',
if(aggr(min(ComponentFormat),COMPONENT_NAME_RELATED_INFO) = 4, '#,##0.00;(#,##0.00)',
if(aggr(min(ComponentFormat),COMPONENT_NAME_RELATED_INFO) = 5, '#,##0.00%',
if(aggr(min(ComponentFormat),COMPONENT_NAME_RELATED_INFO) = 6, '#,##0.0;(#,##0.0)',
if(aggr(min(ComponentFormat),COMPONENT_NAME_RELATED_INFO) = 7, '#,##0.000;(#,##0.000)'
)))))))

I tried this as well and same exact results as below. Switched to using the ID instead of name field and removed Min(). I also tried it without the aggr() function.

if(aggr((ComponentFormat),METRIC_COMPONENT_PK_ID) = 2, '#,##0%',
if(aggr((ComponentFormat),METRIC_COMPONENT_PK_ID) = 3, '#,##0.0%',
if(aggr((ComponentFormat),METRIC_COMPONENT_PK_ID) = 4, '#,##0.00;(#,##0.00)',
if(aggr((ComponentFormat),METRIC_COMPONENT_PK_ID) = 5, '#,##0.00%',
if(aggr((ComponentFormat),METRIC_COMPONENT_PK_ID) = 6, '#,##0.0;(#,##0.0)',
if(aggr((ComponentFormat),METRIC_COMPONENT_PK_ID) = 7, '#,##0.000;(#,##0.000)'
)))))))

Expresssion Formula:

num(
max({<[Year/Month],CURRENT_YEAR_INDICATOR = {'Y'},CURR_IND,[Month No] = {1}>}[Component Value])
,'$(vComponentFormat)')

Results:

Message was edited by: Deborah Pyykkonen. Added scrambled version of app

Re: Number formating with Variable Not working

Fri, 11 Mar 2016 16:45:16 GMT

And, YES, I have it set to Expression Default in Numbers tab.

Re: Number formating with Variable Not working

Peter_Cammaert — Fri, 11 Mar 2016 17:12:24 GMT

It's awfully difficult to figure out which one of the many things that could go wrong is causing this issue. Care to upload an example document that shows this behavior?

Complex expressions not working when more than one value is active usually points to one part of the expression returning multiple results instead of the expected single one. A trick that may prove useful: can you break down your expression into its major parts, and display the result of these expression pieces along with the remainder of the object you want to fix? That way you can observe the behavior of - for instance - the aggr() function in the context of the (unknown) dimensions you are using.

Best,

Peter

Re: Number formating with Variable Not working

Digvijay_Singh — Fri, 11 Mar 2016 17:20:16 GMT

"I created a variable to assign formats to each value based on the value ID."

Not sure but I am not able to understand where ID is being used in variable definition.

Also as we know Aggr function creates virtual table of values against defined dimension, as in your case -

aggr(min(ComponentFormat),COMPONENT_NAME_RELATED_INFO) = 1

you are creating virtual table of minimum values of Component Format for each COMPONENT_NAME_RELATED_INFO

Not sure how it will work if you have multiple "COMPONENT_NAME_RELATED_INFO" , it may work if you have single COMPONENT_NAME_RELATED_INFO.

You may need Min outside Aggr so that left side of equation results to single value.

Re: Number formating with Variable Not working

Fri, 11 Mar 2016 17:27:41 GMT

I updated the expression to use the METRIC_COMPONENT_PK_ID instead of the name. It is a 1:1 however. But I get the same results.

Re: Number formating with Variable Not working

martinpohl — Fri, 11 Mar 2016 17:30:22 GMT

I think it's a problem with "="

You mustn't use it in your variable, but have to in your expression ($(=vComponentFormat)

Regards

Re: Number formating with Variable Not working

Fri, 11 Mar 2016 17:51:58 GMT

Thank you Martin. I tried that as well and no luck.

Re: Number formating with Variable Not working

Fri, 11 Mar 2016 17:53:39 GMT

I attached a scrambled version of the app. with a couple differ variables I tried using. You sill see that the chart above works perfectly with the vMetricFormat but there is always one metric code selected so it is not a problem. The Components are always multiple and that appears to be the issue.

Re: Number formating with Variable Not working

Fri, 11 Mar 2016 18:02:02 GMT

Thank you Digvijay. I tried both inside and outside aggr() but no luck. I must be missing something simple. I have not used the aggr() function much.

Re: Number formating with Variable Not working

martinpohl — Fri, 11 Mar 2016 18:02:19 GMT

use this

=num(max({<[Year/Month],CURRENT_YEAR_INDICATOR = {'Y'},CURR_IND,[Month No] = {1}>}[Component Value]),$(=vTest1))

Regards

Re: Number formating with Variable Not working

Fri, 11 Mar 2016 18:12:02 GMT

Thank you! I tried it with vTest1 as in your example above and it did not work . SO I replaced vTest1 with vComponentFormat and it worked perfectly.

Re: Number formating with Variable Not working

Fri, 11 Mar 2016 18:13:52 GMT

I must have had one thing out of place when I tried this. Thank you