Set analysis in charts and Pivot table expressions

[sspawar88]

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 ?

[PrashantSangle]

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

[ramoncova06]

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

[sspawar88]

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.

[PrashantSangle]

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

[jagan]

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

OR

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.

[sspawar88]

Hi,

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

[jagan]

Hi Satish,

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

[jagan]

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)

[sspawar88]

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