Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Number and Percentage format in a Straight Table

Hi,

I have been trying to figure this out all morning so I hope someone can help me!

I have a straight table and it lists all the KPI's which can be either a number or a percentage. In the table I use the expression below. But there are two number KPI's that it keeps formatting as a percentage, if I select them on their own they show as a number, but I need them to be a number in the main table as well. They are 1's but they keep coming up as 100%. If I don't have '#,##0%' in the expression it shows them as 1.0 which would be fine but I need the percentage sign in for all the other figures, as soon as I do that, it changes these two particular KPI's to 100%!

Hope someone can help, I have run out of ideas.

=
if(Format_KPI = '#' And KPI_Order <> '3.12' And KPI_Order <> '3.18',
Aggr(
Num(
sum({$<Current_Month={"$(=max(Current_Month))"}>} Numerator)
,KPI_Actual_Format)
,KPI_Description),

if(Format_KPI='%',
Aggr(
Num(
sum({$<Current_Month={"$(=max(Current_Month))"}>} Numerator)/sum({$<Current_Month={"$(=max(Current_Month))"}>} Denominator)
,KPI_Actual_Format)
,KPI_Description),

if(KPI_Order='3.12', //it is a number but an average
Aggr(
Num(
sum({$<Current_Month={"$(=max(Current_Month))"}>} Numerator)/sum({$<Current_Month={"$(=max(Current_Month))"}>} Denominator)
,KPI_Actual_Format)
,KPI_Description),

if(KPI_Order='3.18', //it is a number but an average
Aggr(
Num(
sum({$<Current_Month={"$(=max(Current_Month))"}>} Numerator)/sum({$<Current_Month={"$(=max(Current_Month))"}>} Denominator)
,KPI_Actual_Format)
,KPI_Description))
)
)
)

3 Replies
marcus_sommer

I think it's unnecessarily complicated and the nested if's with aggr inside will consume a lot of ressources. I suggest something like this:

simplyfied: num(aggr(), Format)

If Format isn't already matched to the dimension then:

if(condition, Format1, Format2)

     or maybe

pick(match(), Format1, Format2, Format3 ,...)

Try this:

Num(Aggr(

sum({$<Current_Month={"$(=max(Current_Month))"}>} Numerator) /

if(Format_KPI='%' or match(KPI_Order, '3.12', '3.18'),

     sum({$<Current_Month={"$(=max(Current_Month))"}>} Denominator), 1)

,KPI_Description),

,KPI_Actual_Format)

If the formatting isn't correct then KPI_Actual_Format isn't correct or unique (is there more then one field-value available then returned a Field call NULL without an aggregation-function).

- Marcus

anlonghi2
Creator II
Creator II

Hi Katy,

try the below expression, based on Marcus solutions.

If it works fine you have to look at KPI_Actual_Format values to solve your format error.

Best regards

Andrea

Num(
Aggr(
sum({$<Current_Month={"$(=max(Current_Month))"}>} Numerator) / if(Format_KPI='%' or match(KPI_Order, '3.12', '3.18'), sum({$<Current_Month={"$(=max(Current_Month))"}>} Denominator), 1)
,
KPI_Description),
if( Format_KPI='%','##0,00%','#.##0,00'))



Not applicable
Author

Hi, Thank you both for your solutions. But none of the expressions fix the problem I'm afraid.

After more digging, I have found that it seems to be one KPI that is always 100% which is causing the problem. Any other KPI’s that have a number 1, always convert to a 100%. I’ve attached an image (bottom image is where I have excluded the 1.4 KPI which is always 100% and the figures are formatted correctly). Do you know of any way I can prevent this from happening?

Thanks so much for your help and I will definitely change my expression to one of these if it will improve performance.

Kate

Screen Shot Qlik.PNG