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

Multiple Number formats in pivot

Hi All,

Is it possible to have two number formats in a pivot?

For Example, the measure in my pivot is;

if(dimensionality() = 1
 ,(Sum(Aggr(If(sum([Output]) >= (sum(Plan)*.90) and sum([Output]) <= (sum(Plan)*1.10), 1, 0),[Month],Product,Category)))
/count(aggr(count(Product),[Month],Product,Category))
 
 ,if(dimensionality() = 2
     ,if(sum([Output])>= (sum(Plan)*.90) and sum([Output]) <= (sum(Plan)*1.10) ,1,0)
        ,0
       )
  )

 

I want the first dimension (Category) to be in % and the second dimension (Product) to be int (1 or 0).

I have tried to wrap the num function around the expressions but this doesnt seem to be working for me..

e.g Num(Expression,'#,##0%')

 

Any help would be much appreciated!

Thanks!

Labels (2)
1 Solution

Accepted Solutions
sunny_talwar

May be try this

if(dimensionality() = 1
 ,(Num(Sum(Aggr(If(sum([Output]) >= (sum(Plan)*.90) and sum([Output]) <= (sum(Plan)*1.10), 1, 0),[Month],Product,Category)))
/count(aggr(count(Product),[Month],Product,Category)), '##.%')
 
 ,if(dimensionality() = 2
     ,Num(if(sum([Output])>= (sum(Plan)*.90) and sum([Output]) <= (sum(Plan)*1.10) ,1,0), '##.')
        ,0
       )
  )

Make sure to use Measure expression for Number Formatting within the properties of your Expression

View solution in original post

2 Replies
sunny_talwar

May be try this

if(dimensionality() = 1
 ,(Num(Sum(Aggr(If(sum([Output]) >= (sum(Plan)*.90) and sum([Output]) <= (sum(Plan)*1.10), 1, 0),[Month],Product,Category)))
/count(aggr(count(Product),[Month],Product,Category)), '##.%')
 
 ,if(dimensionality() = 2
     ,Num(if(sum([Output])>= (sum(Plan)*.90) and sum([Output]) <= (sum(Plan)*1.10) ,1,0), '##.')
        ,0
       )
  )

Make sure to use Measure expression for Number Formatting within the properties of your Expression

odo_nogk
Contributor
Contributor
Author

Thank you! - Assigning the "measure expression" for number formatting did the trick.