topic Re: Set analysis in charts and Pivot table expressions in QlikView

Set analysis in charts and Pivot table expressions

sspawar88 — Mon, 20 Jul 2015 04:49:32 GMT

in one Bar chart in which dimension is region_name and expression is :

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

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

in another bar chart in which dimension is branch_type and expression is :

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

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

In Pivot table i have two dimensions region_name and branch_type and expression is :

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

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

Can i get the same value in pivot table same as both bar charts ?

Re: Set analysis in charts and Pivot table expressions

PrashantSangle — Mon, 20 Jul 2015 04:56:38 GMT

Hi,

Try below in pivot table

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

Count({TOTAL<region_name,branch_type><param_name={'Temperature'},param_value={'<=-1'}>}param_value)*100

Regards

Re: Set analysis in charts and Pivot table expressions

ramoncova06 — Mon, 20 Jul 2015 04:59:05 GMT

I am not sure I get your request, do you want to use both dimensions/metrics in the same pivot table and get the results that you were getting in from the straight tables ?

by default you would not get the same values since you are adding a second dimension on which the values is broken into a more finer granularity

though you could use a total

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

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

Re: Set analysis in charts and Pivot table expressions

sspawar88 — Mon, 20 Jul 2015 06:02:37 GMT

i'm using stacks in bar chart.

those stack values are not match with values in Pivot table.

thats why i'm asking to verify those formula but it'll not gives proper result.

by the way your formula gives the error.

Re: Set analysis in charts and Pivot table expressions

PrashantSangle — Mon, 20 Jul 2015 06:05:44 GMT

Hi,

my bad take TOTAL<region_name,branch_type> outside set analysis.

try below

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

Count(TOTAL<region_name,branch_type>{<param_name={'Temperature'},param_value={'<=-1'}>}param_value)*100

Regards

Re: Set analysis in charts and Pivot table expressions

jagan — Mon, 20 Jul 2015 06:29:22 GMT

No, you need to mention give total in the expression

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

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

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

Count({<param_name={'Temperature'},param_value={'<=-1'}>} TOTAL<region_name,branch_type> param_value)*100

Use whichever suits you.

Regards,

Jagan.

Re: Set analysis in charts and Pivot table expressions

sspawar88 — Mon, 20 Jul 2015 06:37:21 GMT

Hi,

can you please assist me in one scenario in which i had faced some issue which i could not able to resolve ?

Re: Set analysis in charts and Pivot table expressions

jagan — Mon, 20 Jul 2015 07:00:16 GMT

Hi Satish,

Attach some sample data and your expected output? So that it would be easier to understand and provide the solution.

Re: Set analysis in charts and Pivot table expressions

jagan — Mon, 20 Jul 2015 07:07:14 GMT

Hi,

Sample Data:

Region, Branch, Value

1, 11, 10

1, 12, 20

2, 21, 15

2, 22, 10

For this expression the output will be

Count(Value)/

Count(TOTAL Value)*100

Region, Branch, Value

1, 11, 25 (1/4 * 100)

1, 12, 25 (1/4 * 100)

2, 21, 25 (1/4 * 100)

2, 22, 25 (1/4 * 100)

For this expression the output will be

Count(Value)/

Count(TOTAL<Region, Branch> Value)*100

Region, Branch, Value

1, 11, 10 (1/1 * 100)

1, 12, 20 (1/1 * 100)

2, 21, 15 (1/1 * 100)

2, 22, 10 (1/1 * 100)

Re: Set analysis in charts and Pivot table expressions

sspawar88 — Mon, 20 Jul 2015 07:11:35 GMT

I'm giving link here which i have been posted some days before.

its lengthy but please have a look.

i have attached a sample QVW.

set analysis