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: 
Not applicable

Multiple number formats in one expression

Hi,

I am using a straight table with one dimension and multiple expressions. Depending on the value of the dimension, I would like to use a differen number format.

The dimension is called [Tech Name] and the expressions look something like:

if([Tech Name]= 43, sum(expr1),
if([Tech Name]= 44, sum(expr2),

if([Tech Name]= 45, sum(expr3),
if([Tech Name]= 46, sum(expr4)

))))

Now I would like to have integer values for 43, 44 and 46, and percentages for 44.

Is this possible?

if ([Tech Name]=43, sum({< EA.Z_Risk_Grade = {'H'}, eng_costcenter_key = {$(vCostCenter)},statusCode={'CLO'},
    engagementstatus_start_month = {'$(=date($(vReportingDate)))'}>} eng_net_bill_value_PTD/1000)
if ([Tech Name]=44,
    (sum({< eng_costcenter_key = {$(vCostCenter)},statusCode={'CLO'},engagementstatus_start_month = {'$(=date($(vReportingDate)))'}>} (eng_net_bill_value_PTD))
    /
    sum({< eng_costcenter_key = {$(vCostCenter)},statusCode={'CLO'},engagementstatus_start_month = {'$(=date($(vReportingDate)))'}>} (eng_time_wip_PTD))),
if([Tech Name]=45,
    sum(
    aggr(
        if(sum({< eng_costcenter_key = {$(vCostCenter)}, hours_month= {'>=$(=Date(v46_date_from))<=$(=Date(vReportingDate))'},
            engagement_max_end_date_empty_flag = {1}  >}
            timeAmount) =0 ,1)       
    ,engagementId, [KPI name]))
,

if( [Tech Name]=46,
    count({< CA.costcenterId= {$(vCostCenter)},CA.ass_start_month = {'$(=date($(vReportingDate)))'}, CA.status = {'Discontinued'}>} CA.assessmentId)
    +
    count({< EA.costcenterId= {$(vCostCenter)},EA.ass_start_month = {'$(=date($(vReportingDate)))'}, EA.status = {'Discontinued'}>} EA.assessmentId)
           
))))

if ([Tech Name]=43, sum({< EA.Z_Risk_Grade = {'H'}, eng_costcenter_key = {$(vCostCenter)},statusCode={'CLO'},
    engagementstatus_start_month = {'$(=date($(vReportingDate)))'}>} eng_net_bill_value_PTD/1000)
if ([Tech Name]=44,
    (sum({< eng_costcenter_key = {$(vCostCenter)},statusCode={'CLO'},engagementstatus_start_month = {'$(=date($(vReportingDate)))'}>} (eng_net_bill_value_PTD))
    /
    sum({< eng_costcenter_key = {$(vCostCenter)},statusCode={'CLO'},engagementstatus_start_month = {'$(=date($(vReportingDate)))'}>} (eng_time_wip_PTD))),
if([Tech Name]=45,
    sum(
    aggr(
        if(sum({< eng_costcenter_key = {$(vCostCenter)}, hours_month= {'>=$(=Date(v46_date_from))<=$(=Date(vReportingDate))'},
            engagement_max_end_date_empty_flag = {1}  >}
            timeAmount) =0 ,1)       
    ,engagementId, [KPI name]))
,

if( [Tech Name]=46,
    count({< CA.costcenterId= {$(vCostCenter)},CA.ass_start_month = {'$(=date($(vReportingDate)))'}, CA.status = {'Discontinued'}>} CA.assessmentId)
    +
    count({< EA.costcenterId= {$(vCostCenter)},EA.ass_start_month = {'$(=date($(vReportingDate)))'}, EA.status = {'Discontinued'}>} EA.assessmentId)
           
))))

Now

1 Solution

Accepted Solutions
lironbaram
Partner - Master III
Partner - Master III

hei

write

if([Tech Name]= 43,num( sum(expr1),'#,##0'),
if([Tech Name]= 44,num( sum(expr2),'#,##0'),

if([Tech Name]= 45, num( sum(expr3),'#,##0%'),
if([Tech Name]= 46, num( sum(expr4),'#,##0')

))))

in the number tab in the properties

choose for the expression - Expression Default

View solution in original post

2 Replies
lironbaram
Partner - Master III
Partner - Master III

hei

write

if([Tech Name]= 43,num( sum(expr1),'#,##0'),
if([Tech Name]= 44,num( sum(expr2),'#,##0'),

if([Tech Name]= 45, num( sum(expr3),'#,##0%'),
if([Tech Name]= 46, num( sum(expr4),'#,##0')

))))

in the number tab in the properties

choose for the expression - Expression Default

Not applicable
Author

Too easy!

I thought I tried that before, but apparently I did not put the expression number format on expression default...

Thanks.