Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Number formating with Variable Not working

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:

Capture.JPG

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

1 Solution

Accepted Solutions
Partner
Partner

Re: Number formating with Variable Not working

use this

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

Regards

11 Replies
Not applicable

Re: Number formating with Variable Not working

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

Re: Number formating with Variable Not working

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

Digvijay_Singh
Honored Contributor III

Re: Number formating with Variable Not working

"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.


Not applicable

Re: Number formating with Variable Not working

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.

Partner
Partner

Re: Number formating with Variable Not working

I think it's a problem with "="

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

Regards

Not applicable

Re: Number formating with Variable Not working

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

Highlighted
Not applicable

Re: Number formating with Variable Not working

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. 

Not applicable

Re: Number formating with Variable Not working

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.

Partner
Partner

Re: Number formating with Variable Not working

use this

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

Regards