Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Partner
Partner

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
Highlighted
Not applicable

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

Highlighted

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

Highlighted
Master III
Master III

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


Highlighted
Not applicable

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.

Highlighted
Partner
Partner

I think it's a problem with "="

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

Regards

Highlighted
Not applicable

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

Highlighted
Not applicable

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. 

Highlighted
Not applicable

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.

Highlighted
Partner
Partner

use this

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

Regards

View solution in original post