Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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
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
Highlighted
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
Highlighted
Contributor
Contributor

Hi Channa,

 

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

Highlighted
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?

Highlighted
Contributor
Contributor

Hi Tresesco,

Thanks! Attached is the sample. 

Highlighted
Creator
Creator

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)
)

 

 

Highlighted
Contributor
Contributor

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.

Highlighted
Partner
Partner

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.

Highlighted
Contributor
Contributor

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!