topic Set analysis in QlikView

Set analysis

sspawar88 — Tue, 07 Jul 2015 06:57:27 GMT

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 ?

Re: Set analysis

Tue, 07 Jul 2015 07:12:52 GMT

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.

Re: Set analysis

MayilVahanan — Tue, 07 Jul 2015 07:17:17 GMT

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)

Re: Set analysis

sspawar88 — Tue, 07 Jul 2015 07:34:51 GMT

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

here now i have both pivot table

Re: Set analysis

jonathandienst — Tue, 07 Jul 2015 07:35:23 GMT

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

Re: Set analysis

sspawar88 — Tue, 07 Jul 2015 07:41:02 GMT

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

Re: Set analysis

jonathandienst — Tue, 07 Jul 2015 07:43:38 GMT

Perhaps it should have been total <region_name>?

Re: Set analysis

sspawar88 — Tue, 07 Jul 2015 07:52:25 GMT

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 ?

Re: Set analysis

Tue, 07 Jul 2015 08:10:28 GMT

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

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

Re: Set analysis

sspawar88 — Tue, 07 Jul 2015 08:28:22 GMT

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)