Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
martinpohl
Partner - Master
Partner - Master

use this

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

Regards

View solution in original post

11 Replies
Not applicable
Author

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

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

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.

martinpohl
Partner - Master
Partner - Master

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
Author

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

Not applicable
Author

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
Author

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.

martinpohl
Partner - Master
Partner - Master

use this

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

Regards