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: 
Shaq
Contributor
Contributor

How to concatenate sign( CHR 9650 & CHR 9660 ) with number as a conditional number in set analysis

Hi,

I would like to include an increase/decrease sign next to KPI to indicate that the figure has increased/decreased when compared to another figure. 

For example, sales 2018=2000 and sales 2017=1500. Hence my KPI should show " CHR(9650) 2000". But let say if the sales 2018 = 500 then, it should show " CHR(9660) 500". 

Currently, I'm using the below set analysis but it doesn't show anything even if the syntax is OK. Attached are the screenshots 2.png (what it should be) and 3.png (my output) .

If(
Avg({<Year={$(=max(Year))}>} [RoomBooking.Occupancy Rate]) > Avg({<Year={$(=max(Year)-1)}>} [RoomBooking.Occupancy Rate]),
Num(Avg({<Year={$(=max(Year))}>} [RoomBooking.Occupancy Rate])/100 ,chr(9650) & '#,##0.0%'),
Num(Avg({<Year={$(=max(Year))}>} [RoomBooking.Occupancy Rate])/100 ,chr(9660) & '#,##0.0%')
)

Thanks!

8 Replies
Channa
Specialist III
Specialist III

If(
Avg({<Year={$(=max(Year))}>} [RoomBooking.Occupancy Rate]) > Avg({<Year={$(=max(Year)-1)}>} [RoomBooking.Occupancy Rate]),


Num(Avg({<Year={$(=max(Year))}>} [RoomBooking.Occupancy Rate])/100 , '#,##0.0%')&chr(9650) ,

or 

concate(Num(Avg({<Year={$(=max(Year))}>} [RoomBooking.Occupancy Rate])/100 , '#,##0.0%'),chr(9650))
Num(Avg({<Year={$(=max(Year))}>} [RoomBooking.Occupancy Rate])/100 ,chr(9660) & '#,##0.0%')
)

Channa
Shaq
Contributor
Contributor
Author

Hi Channa,

 

Thanks for the reply but it doesn't seem correct as the character needs to be concatenated to be in format number.

tresesco
MVP
MVP

Your expression seems to be fine (unless I am missing small things like parenthesis issue...). Could you share your screen shots or a sample app to investigate?

Shaq
Contributor
Contributor
Author

Hi Tresesco,

Thanks! Attached is the sample. 

anat
Master
Master

can u try like below

If(
Avg({<Year={$(=max(Year))}>} [RoomBooking.Occupancy Rate]) > Avg({<Year={$(=max(Year)-1)}>} [RoomBooking.Occupancy Rate]),
Num(Avg({<Year={$(=max(Year))}>} [RoomBooking.Occupancy Rate])/100 , '#,##0.0%')&chr(9650),
Num(Avg({<Year={$(=max(Year))}>} [RoomBooking.Occupancy Rate])/100 , '#,##0.0%')&chr(9660)
)

 

 

Shaq
Contributor
Contributor
Author

Hi Anat,

Thanks for your reply and help :):) !!

It is working to change the sign to up and down..but it is not in number format as I need it in number format to use conditional color measure (to use this the measure needs to be in number format). Kindly refer the screenshot.

gmenoutis
Partner - Creator II
Partner - Creator II

Considering

  1. Since you want the format code to change, no reason to include the kpi calculation within the conditional statement
  2. Since you want a +/0/- style case on a specific number (avg()-avg(), let's use sign (output: -1/0/1), add 2 (new output:1/2/3, useable with pick) to select a format code prefix

Num(
Avg({<Year={$(=max(Year))}>} [RoomBooking.Occupancy Rate])/100
, pick(
2+sign(Avg({<Year={$(=max(Year))}>} [RoomBooking.Occupancy Rate]) - Avg({<Year={$(=max(Year)-1)}>} [RoomBooking.Occupancy Rate]))
,chr(9660),'',chr(9650)
) & '#,##0.0%'
)

Note that the original selections fall into the = case, so you'll see no arrows.

Shaq
Contributor
Contributor
Author

Hi Gmenoutis,

Thanks for the help! 

The reason I need to use the conditional format is to set the colour of the KPI. However, somehow I got the feeling that we could set the colour in the set analysis too but I just didn't find the way to do so. Any ideas on how to include that? 

For example, if the KPI is increasing I need to have it in green colour while decreasing in red colour.

Your help is appreciated 🙂

Thanks!