How do i display all?

[n1ef5ng1]

Hi,

Example, I have a field name call City which comprises all the cities all over the world

Firstly, I put this statement on the dimension tab

if(city = 'london','london','Others')

This will tabulate two column, London and Others(All cities exclude London)

However, here is the twist,

My desired output is two columns with London and Others(All including London)

How do I derived this, kinda urgent. Thank you

[alexandros17]

Try to use my solution, it works and it is easy to use ...

[n1ef5ng1]

(i have never added anything from the script before)

till date, i did all my stuff through dimension and expression

can you teach step by step, appreciate alot

[calvindk]

you can do this:

if(city = 'london',City) // this will be london only

if(city = '*', 'All') // this will be all cities

[alexandros17]

You have loaded a field named city, you find it in a load or select script statement.

Copy the row and modify it as follows:

Example

Load (or SELECT)

...

...

,,,

city,

...

From (or Resident)

     ....

Where

     ....

the transform to

Load (or SELECT)

...

...

,,,

city,

city as new City,

...

From (or Resident)

     ....

Where

     ....

this double city informations and you can use it as another column

Hope it helps

[n1ef5ng1]

where do you put this? on the dimension? both if?

[#7 · Accepted solution]

Try

Dimension : Valuelist('London','Others')

Expression : if(Valuelist('London','Others')='London',

                         sum({<city={'London'}>}field1),sum({<city={'*'}>}field1))

The {'*'} will be all cities where there is a value. {'*'} also excludes nulls from the calculation

Should give you what you looking for mate

Cheers,

Byron

[n1ef5ng1]

what is field1?

[#9]

replace field1 with the field you have in your document that you want to sum or count i.e. Revenue, Cost et cetera

[n1ef5ng1]

thanks alot

Dimension : Valuelist('London','Others')

Expression : if(Valuelist('London','Others')='London',

                         sum({<city={'London'}>}field1),sum({<city={'*'}>}field1))

say if i want to have three value list instead, london, other, % of shares

how do i fix it on the expression?

[#11]

Heya,

Valuelist if very resource intensive so always try and find alternative solutions if working with a really large dataset.

To add, just include it in the dimension separated by a comma e.g.

Dimension: Valuelist('London','Others','% of Shares')

Expression:

if(Valuelist('London','Others','% of Shares')='London',

     sum({<city={'London'}>}field1),

if(Valuelist('London','Others','% of Shares')='Others',

     null(),

if(Valuelist('London','Others','% of Shares')='% of Shares',

     num(sum({<city={'London'}>}field1)/sum(TOTAL {<city={'London'}>}field1),'#.00%')))

Hope that helps mate

Cheers,

Byron