topic Re: Advanced Aggregation function in QlikView

Advanced Aggregation function

sspawar88 — Fri, 31 Jul 2015 12:34:04 GMT

Hi community,

Aggr(nodistinct count(param_value),branch_type)

Can i add two dimension like this

Aggr(nodistinct count(param_value),branch_type,branch_name)

it is not working.

Re: Advanced Aggregation function

sorrakis01 — Fri, 31 Jul 2015 12:39:23 GMT

Hi,

The expression it's right. Maybe you need check your data

Re: Advanced Aggregation function

marcus_sommer — Fri, 31 Jul 2015 12:40:42 GMT

Yes, you could use more then one dimension, maybe you need a further aggregation to get your result and you should try if nodistinct really covered your aim:

sum(Aggr(nodistinct count(param_value),branch_type,branch_name))

- Marcus

Re: Advanced Aggregation function

sspawar88 — Fri, 31 Jul 2015 12:46:37 GMT

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

/Aggr(Nodistinct count({<param_name={'TEMP'}, param_value={'<=-1'}>}param_value),branch_type)

i'm using this expression it shows branch_type 100% in stack bar chart which is correct.

but my dimension is group branch_type,branch_name, loc_code.

when i drilled it level 2 and level 3. they are not 100% which is not correct.

How can i make change in my expression?

Re: Advanced Aggregation function

sspawar88 — Fri, 31 Jul 2015 12:46:50 GMT

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

/Aggr(Nodistinct count({<param_name={'TEMP'}, param_value={'<=-1'}>}param_value),branch_type)

i'm using this expression it shows branch_type 100% in stack bar chart which is correct.

but my dimension is group branch_type,branch_name, loc_code.

when i drilled it level 2 and level 3. they are not 100% which is not correct.

How can i make change in my expression?

Re: Advanced Aggregation function

marcus_sommer — Fri, 31 Jul 2015 12:56:07 GMT

On a lower data-level there could be missing data or NULL or 0 as divident. You could try to fetch it with functions like alt(). Helpful could further be to copy this chart, turn it in a chart-table and use each expression part as single-expression, too see if it returned your expected values.

- Marcus

Re: Advanced Aggregation function

sspawar88 — Fri, 31 Jul 2015 13:08:43 GMT

Hi Marcus,

i'm using group of dimensions for drilled down purpose.

at first level of branch_type it shows 100% which is correct but at second level it is not showing 100% that is not correct.

i'm trying to get 100% on each level of chart from my expression

Each branch_type has a branch_names

Re: Advanced Aggregation function

marcus_sommer — Fri, 31 Jul 2015 15:27:25 GMT

By a dimension-group you need to query the current dimension and use them as dimension with your aggr:

aggr(YourExpression, $(=getcurrentfield(YourDimensionGroup)))

- Marcus