Dynamic formatting of a bar chart

[joeybird]

Hiya

Qlik Sense

is there a way of dynamically changing a format of a bar chart depending on its values....

some rows of data are for percentages

apples 75%

workers 1008  and some are just number values

when a bar chart is used its formatted for percentages...when the selected apples

however when someone selected workers...its showing in percentage (it would)

is there a way to dynamically change the formatting if a "Format"column was added with eg... number or percentage?

could I use expression formatting code similar to this ? and if yes... is this correct way to do it???

IF(([TargetValue] AND [Format] = 'Percentage')

OR ([TargetValue] AND [Format] = 'Number'), %(), Number())

or can a small drop down be added? users can use on the sheet of the app and just select format percentage or number? this will dynamically change the formatting of the bar chart?

any ideas please?

Kind Regards

Joeybird

[JonnyPoole · Accepted solution]

Hi - i did get this to work. 

1. Build a 2 row (or more) data island table in the data model with each row determining a 'format code'

Format:

load

  if(RowNo()=1,'Percentage','Integer') as Format

AutoGenerate 2;   

2. Build a variable to determine the numeric format based on the selection:

set vFormat='if(Only(Format)=' & chr(39) & 'Integer' & chr(39) &',' &chr(39)& '#,##0' & chr(39) & ',' & chr(39) & '#,##0%' & chr(39) & ')';

3. Invoke the variable in the 2nd argument of the num() function to dynamically determine the format.

change measure expression from:

sum(Sales)

to:

num( sum(Sales), $(= chr(39) & $(=$(vFormat)) & chr(39)) )

and leave format to 'auto

[joeybird]

Hiya

this code worked amazing, however it does not like below

load

  if(RowNo()=1,'Percentage','Integer') as Format

AutoGenerate 2; 

all I did was create the extra column called format, and said if the row was Percentage or Integer

then used all the rest of the instructions and hay presto.

Thanks so much x

Kind Regards

Joeybird

[sabreyals]

Hi JonnyPoole,

   Thanks for your solution below, it brings me close to resolving my issue but i am still missing something. I am working on QlikSense April,2019 and running into an issue with dynamic formatting in Line chart. The expression seems to work fine in a table box though. 

My user needs to cycle through 50+ extensions via a drop down and for each measure selected needs to show its trend over time on a line chart. I used an inline table and created 50 measures and in the front end i use pick match as in below example.

pick(match(Measure,1,2,3),

num(sum(A),'#,###.00'),

num(Sum(B),'$#,###.00'),

num(Sum(C),'##.#0%')

)

The number and currency format work fine , the percentage format however does not convert on the y axis. If you hover over the value points they show up in percentage format but the axis does not.

I have used your solution by creating a new field in the inline table called MetricFormat and its values are like Integer,Percentage.I then created a variable vFormat in the front end like this

='if(Only(MetricFormat)=' & chr(39) & 'Integer' & chr(39) &',' &chr(39)& '#,##0' & chr(39) & ',' & chr(39) & '#,##0%' & chr(39) & ')'

If I pull this variable in a text object i can see it is working as expected for Measure 3 i.e. it shows percent format.

Inside the Line chart expression i use it as below but still the axis doesnt reflect percent format

num(sum(C),$(=chr(39)&$(=$(vFormat))&chr(39)))

 the validation popup in the expression editor doesn't seem to be finding the string after the comma. it just sees num(sum(C), ) Any ideas what i am doing wrong?