Set analysis

[sspawar88]

Hi Community,

i'm using pivot table in which having a following expression for region it shows 100% which is correct.

but when i put branch_type dimension in it they too become 100% which is wrong.

Count({<param_name={'Temperature'},region_name={$(=Concat(DISTINCT chr(39)&region_name&chr(39),','))},

param_value={'<=-1'}>}param_value)

/

Count({1<param_name={'Temperature'},region_name={$(=Concat(DISTINCT chr(39)&region_name&chr(39),','))},

param_value={'<=-1'}>}param_value)

in above circle..the sum of percentage has to become 100% for WEST and same for SOUTH.

what changes needs to be done in set analysis ?

[#2]

Try this one,

Count({<param_name={'Temperature'},region_name={$(=Concat(DISTINCT chr(39)&region_name&chr(39),','))},

param_value={'<=-1'}>}param_value)

/

Count({1<param_name={'Temperature'},region_name={$(=Concat(DISTINCT chr(39)&region_name&chr(39),','))},

param_value={'<=-1'}>} Total param_value)

Added total in expression.

[MayilVahanan]

Hi

In your expression,

Count({<param_name={'Temperature'},region_name={$(=Concat(DISTINCT chr(39)&region_name&chr(39),','))},

param_value={'<=-1'}>}param_value)

/

Count({1<param_name={'Temperature'},region_name={$(=Concat(DISTINCT chr(39)&region_name&chr(39),','))},

param_value={'<=-1'}>}param_value)

Both containst the same value. So its shows like 100%. If u select any values in any filters, than u can able to see the difference.

Try like this

Count({<param_name={'Temperature'},region_name={$(=Concat(DISTINCT chr(39)&region_name&chr(39),','))},

param_value={'<=-1'}>}param_value)

/

Count({1<param_name={'Temperature'},region_name={$(=Concat(DISTINCT chr(39)&region_name&chr(39),','))},

param_value={'<=-1'}>} TOTAL param_value)

[sspawar88]

But can i put the only one formulae in PIVOT to show my required result.

here now i have both pivot table

[jonathandienst]

You need a TOTAL qualifier in the divisor, not a '1':

Count({<param_name={'Temperature'}, param_value={'<=-1'}>} param_value)

/

Count({<param_name={'Temperature'}, param_value={'<=-1'}>} Total <branch_type> param_value)

The '1' ignores user selections, while the 'Total' ignores the dimensions in the table

[sspawar88]

But then it shows 100% for each branch_type..which is not correct

[jonathandienst · Accepted solution]

Perhaps it should have been total <region_name>?

[sspawar88]

actually my dilemma is to calculate

count all temperature which is less than -1 for region /

Count of all temperature which is less than -1 for region @--which is 100%

                                                   and

count all temperature which is less than -1 for XY branch_type /

Count of all temperature which is less than -1 for region in which XY branch_type locates

so that regions are 100% but branch_types percentage has to sum to 100%

can i have one formulae to workout this in pivot table where dimensions are branch_type and region_type ?

[#9]

You can use dimensionality() function for your expression like,

If(Dimensionality()=1, Your Exp for Region, Your expression for Branch)

[sspawar88]

Count({<param_name={'Temperature'}, param_value={'<=-1'}>} param_value)

/

Count({<param_name={'Temperature'}, param_value={'<=-1'}>} Total <region_name> param_value)

can you explain please what it does actually-------Total <region_name> param_value)