Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

The way to achieve your own success is the willingness to help somebody else. Go for it!

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Sense
- :
- Multiple Number formats in pivot

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

odo_nogk

Contributor

2019-10-08
05:53 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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!

394 Views

1 Solution

Accepted Solutions

sunny_talwar

MVP

2019-10-08
07:32 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2 Replies

sunny_talwar

MVP

2019-10-08
07:32 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2019-10-08
09:06 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

362 Views