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

[Shaq]

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!

[Channa]

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

[Shaq]

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]

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]

Hi Tresesco,

Thanks! Attached is the sample. 

[anat]

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]

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]

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]

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!